Relationship between state, city, municipality and district

Question

I downloaded an IBGE xml file with data from states, cities, municipalities and districts. I separated it into 4 tables and made their relationships.

Follow the Sqlfiddle

My question is:

• It’s right this relationship I’ve made?
• It has how to improve even using Mysql?

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

• This query has how to improve or is already good?
• And when I put one order by nome_estado, nome_cidade, nome_municipio, nome_distrito, with the data I have (a total of 10302) this query goes from 0.006s (an average) to 0.356s (tbm an average), because of the order by. There are ways to improve this 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

Answer 1

Basically lacking index in 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 Join starting from the State, and following the same sequence of the ORDER, but when preprocessing your query, anyway the BD can do the optimization.

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 mine and your query are relatively similar, but there is some difference. The index, in turn, can give a bigger help.

SQL Fiddle updated