MySQL - Optimization select query

Question

My query is taking 5 seconds to finish but it sounds to much for something so basic. I did an explain query:

https://www.dropbox.com/s/18pedm9n5fssz4e/localhost%20_%20localhost%20_%20logrede%20_%20phpMyAdmin%203.4.11.pdf

It is possible to you to help me optimize it?

Thanks :)

Answer 1

I've restructured the query using JOINs instead of a less readable WHERE clause. I see everything is based on the primary "material" table. So, my first suggestion is to use the "STRAIGHT_JOIN" clause to tell MySQL to do the query in the order you have listed.... otherwise, sometimes it tries to think for you and do some things in what could be an alternate "primary" table driving the query. All your other tables are basically "lookup values" tables.

Secondly... On each of the tables, I would specifically have the following indexes on the tables. I'm sure you already had on the primary keys of each table... but since your GROUP_CONCAT are getting a corresponding descriptive field from the table, I would include that column in the index. Here's the other reason... When a query is run and using the index, if the query does not have to go back to the raw table for the "other" columns, it doesn't have to. So, by having the description as part of the index, it can do the join AND get the description both from the index...

I would have an index on

material ( cdomaterial )
cliente ( idcliente, nome )
classeobra ( idclasse, sigla )
dma ( iddma, nome )
desenho( iddesenho, nome )
fornecedor ( idfornecedor, nome )
modelo ( idmodelo, nome )
fabricante ( idfabri, nome )
fornecido( codmaterial, preco )
unidade( idunidade, sigla )
tipofornecimento( idtipoforn, sigla )

So, the above said, I would run the following query...

SELECT STRAIGHT_JOIN
      material.codmaterial, 
      cliente.nome as cliente, 
      tipofornecimento.sigla as fornecimento, 
      unidade.sigla as unidade,
      GROUP_CONCAT(DISTINCT classeobra.sigla ORDER BY classeobra.idclasse SEPARATOR ', ') as classeobra, 
      GROUP_CONCAT(DISTINCT dma.nome ORDER BY dma.iddma SEPARATOR ', ') as dma, 
      GROUP_CONCAT(DISTINCT desenho.nome ORDER BY desenho.iddesenho SEPARATOR ', ') as desenho,
      GROUP_CONCAT(DISTINCT fornecedor.nome ORDER BY fornecedor.idfornecedor SEPARATOR ', ') as fornecedor, 
      GROUP_CONCAT(DISTINCT modelo.nome ORDER BY modelo.idmodelo SEPARATOR ', ') as modelo,
      GROUP_CONCAT(DISTINCT fabricante.nome ORDER BY fabricante.idfabri SEPARATOR ', ') as marca, 
      GROUP_CONCAT(DISTINCT fornecido.preco ORDER BY fornecido.preco SEPARATOR ', ') as preco 
   FROM 
      material
         JOIN unidad
            ON material.idunidade = unidade.idunidade 
         JOIN requisitado
            ON material.codmaterial = requisitado.codmaterial 
            JOIN cliente
               ON requisitado.idcliente = cliente.idcliente 
            JOIN tipofornecimento
               ON requisitado.idtipoforn = tipofornecimento.idtipoforn 
         JOIN possuimodelo
            ON material.codmaterial = possuimodelo.codmaterial 
            JOIN modelo
               ON possuimodelo.idmodelo = modelo.idmodelo 
               JOIN pertence,
                  ON modelo.idmodelo = pertence.idmodelo 
                  JOIN fabricante 
                     ON pertence.idfabri = fabricante.idfabri 
         JOIN utilizadoclasseobra
            ON material.codmaterial = utilizadoclasseobra.codmaterial 
            JOIN classeobra 
               ON utilizadoclasseobra.idclasse = classeobra.idclasse 
         JOIN possuidma
            ON material.codmaterial = possuidma.codmaterial 
            JOIN dma
               ON possuidma.iddma = dma.iddma 
         JOIN fornecido
            ON material.codmaterial = fornecido.codmaterial
            JOIN fornecedor
               ON fornecido.idfornecedor = fornecedor.idfornecedor 
         JOIN possuidesenho
            ON material.codmaterial = possuidesenho.codmaterial 
            JOIN desenho
               ON possuidesenho.iddesenho = desenho.iddesenho 
   GROUP BY
      material.codmaterial, 
      cliente.nome 
   ORDER BY 
      material.codmaterial ASC, 
      cliente.nome ASC ;