Relationship between state, city, municipality and district

Question

Relationship between state, city, municipality and district

Navigation

#1 by (5 votes)

7

I downloaded an IBGE xml file with data from states, cities, municipalities and districts. I made the split for 4 tables and made their relationships.

Follow SQLFiddle

My question is:

Is this relationship right?
Can you still improve using MySQL?

I made a view to make the data search with the query displayed more practical.

Does this query improve or is it already good?
And when I put a order by nome_estado, nome_cidade, nome_municipio, nome_distrito , with the data I have (a total of 10302) this query changes from 0.006s (a mean) to 0.356s (tbm a mean), this because of the order by. Can you improve this sort order?

Follow the query itself:

select  e.id as estado_id, e.nome as nome_estado,
        c.id as cidade_id, c.nome as nome_cidade,
        m.id as municipio_id, m.nome as nome_municipio,
        d.id as distrito_id, d.nome as nome_distrito
from distrito d
join municipio m ON m.id = d.municipio_id
join cidade c ON c.id = m.cidade_id
join estado e ON e.id = c.estado_id

mysql modelo-relacional

asked by anonymous 15.04.2014 / 05:38

1 answer

Changing the contents of the char variable Problems with regular expressions (friendly url)

score 5 · Accepted Answer

Basically missing index on names to improve performance:

CREATE TABLE IF NOT EXISTS 'cidade' (
   'id' int(11) NOT NULL AUTO_INCREMENT,
   'nome' varchar(255) NOT NULL,
   'estado_id' int(11) NOT NULL,
   PRIMARY KEY ('id'),
   KEY 'nome' ('nome'),
   KEY 'estado_id' ('estado_id')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS 'distrito' (
   'id' int(11) NOT NULL AUTO_INCREMENT,
   'nome' varchar(255) NOT NULL,
   'municipio_id' int(11) NOT NULL,
   PRIMARY KEY ('id'),
   KEY 'nome' ('nome'),
   KEY 'municipio_id' ('municipio_id')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS 'estado' (
   'id' int(11) NOT NULL AUTO_INCREMENT,
   'nome' varchar(255) NOT NULL,
   'sigla' varchar(2) NOT NULL,
   PRIMARY KEY ('id'),
   KEY 'nome' ('nome')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS 'municipio' (
   'id' int(11) NOT NULL AUTO_INCREMENT,
   'nome' varchar(255) NOT NULL,
   'cidade_id' int(11) NOT NULL,
   PRIMARY KEY ('id'),
   KEY 'nome' ('nome'),
   KEY 'cidade_id' ('cidade_id')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

In this case, you could also do the join starting from the state, and following the same ORDER sequence, but by preprocessing your query, BD can do the optimization anyway.

I would probably do the query like this, keeping the order similar to the result:

SELECT
   e.id   AS estado_id,
   e.nome AS estado_nome,
   c.id   AS cidade_id,
   c.nome AS cidade_nome,
   m.id   AS municipio_id,
   m.nome AS municipio_nome,
   d.id   AS distrito_id,
   d.nome AS distrito_nome
FROM estado e
LEFT JOIN cidade c ON c.estado_id = e.id
LEFT JOIN municipio m ON m.cidade_id = c.id
LEFT JOIN distrito d ON d.municipio_id = m.id
ORDER BY e.nome,c.nome,m.nome,d.nome

The Execution Plan of my and your query are relatively similar, but it has some difference. The index, in turn, can give a greater help.

SQL Fiddle updated