Select too slow, how to improve?

Question

I have a chart of artisans with about 90,000 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

Answer 1

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);

Answer 2

Tense using the INNER JOIN at the junction of the tables instead of this heap of AND in the 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