Select too slow, how to improve?

Question

Select too slow, how to improve?

Navigation

#1 by (3 votes)
#2 by (3 votes)

3

I have a cadre of craftsmen with about 90 thousand records. I created the SQL below to return me the total of artisans in each of the regions that they are registered. Although it works, it is slow, taking more than 30 seconds to return the records.

How can I improve?

    SELECT
        coordenacao.descricao AS nome, 
        COUNT(artesao.codigo) AS qtde
    FROM 
        municipio, artesao, coordenacaomunicipios, coordenacao
    WHERE 
        artesao.codMunicipio = municipio.codigo AND 
        coordenacaomunicipios.codMunicipio = municipio.codigo AND 
        coordenacaomunicipios.codCoordenacao = coordenacao.codigo AND 
        artesao.codStatus = 1
    GROUP BY 
        coordenacao.descricao
    ORDER BY 
        coordenacao.codigo

mysql sql group-by

asked by anonymous 22.09.2016 / 19:52

2 answers

3

Tense to use INNER JOIN in joining tables instead of this AND no WHERE :

SELECT
        coordenacao.descricao AS nome, 
        COUNT(artesao.codigo) AS qtde
    FROM 
        municipio 
        INNER JOIN artesao ON municipio.codigo = artesao.codMunicipio
        INNER JOIN coordenacaomunicipios ON coordenacaomunicipios.codMunicipio = municipio.codigo
        INNER JOIN coordenacao ON coordenacaomunicipios.codCoordenacao = coordenacao.codigo
    WHERE 
        artesao.codStatus = 1
    GROUP BY 
        coordenacao.descricao
    ORDER BY 
        coordenacao.codigo

22.09.2016 / 20:07

How to list the data of a SQL query grouped by order of data presentation Why explicitly declare the base when calling function when using templates?

score 3 · Accepted Answer

Try to create the mysql search indexes .

ALTER TABLE municipio ADD INDEX(codigo); 
ALTER TABLE artesao ADD INDEX(codMunicipio); 
ALTER TABLE coordenacaomunicipios ADD INDEX(codMunicipio, codCoordenacao );  
ALTER TABLE coordenacao ADD INDEX(codigo, codStatus);