I have many INNER JOIN in the query, can you minimize?

Question

I have a query in a database that uses several INNER JOIN and I realize that in some moments the system slows down in this request, has how I reduced it?

Below follows explanation of my structure

• Table clients with all address data, name and documents;
• Purchase order table containing items, quantity and value;
• Product table containing the features, qty in stock;
• Suppliers table containing all supplier data.

The requested table contains id do produto, id do cliente, and the product table contains o id do fornecedor.

Below follows my sql query in question, which I would like to know how I can improve it, or if that way it is already correct.

SELECT ped_oc.id_pedido as pedido, tbl_clientes.Nome as cliente, tbl_usuarios.Nome as vendedor, fin_fornecedores.NomeFantasia as fornecedor, COUNT(fin_fornecedores.Id) as qtde, ped_oc.data_atualizacao as data 
FROM ped_oc
INNER JOIN tbl_aux_orcamento ON ped_oc.id_orcamento = tbl_aux_orcamento.Id
INNER JOIN tbl_clientes ON tbl_aux_orcamento.Id_cliente = tbl_clientes.Id
INNER JOIN tbl_usuarios ON tbl_aux_orcamento.Id_vendedor = tbl_usuarios.id
INNER JOIN tbl_produtos ON ped_oc.id_modelo = tbl_produtos.Id
INNER JOIN fin_fornecedores ON tbl_produtos.Id_Fornecedor = fin_fornecedores.Id
INNER JOIN ped_pedido ON ped_oc.id_pedido = ped_pedido.id_pedido
INNER JOIN tbl_orcamentos ON tbl_aux_orcamento.Id = tbl_orcamentos.Id_tbl_aux_orcamento
WHERE ped_pedido.status_oc = '1'
AND tbl_orcamentos.status = '1'
AND ped_pedido.id_pedido =  ped_oc.id_pedido
GROUP BY fin_fornecedores.NomeFantasia

Answer 1

I have no knowledge in optimizing queries, but I believe that one way to optimize is with the use of indexes. Check which indexes and if there are any that exist in your tables, with the exception of the primary key (to check the commands in mysql, click here). Once done, try to run select using the reserved word EXPLAIN. Thus, it will show the index(s) that is(s) being used(s). This way, you can act through this information, creating a new index or modifying it according to existing relationships.

I hope I’ve helped.