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

2

I have a query in a database that uses several INNER JOIN and I realize that sometimes the system slows down in this request, how can I reduce it?

  

Below is an explanation of my structure

  • Customers table with all data address, name and documents;
  • Purchase order table containing the items, quantity, and value;
  • Product table containing the characteristics, quantity in stock;
  • Vendor table that contains all vendor data.

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

  

Below is my sql query in question, which would like to know how I can improve it, or if 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
    
asked by anonymous 05.01.2018 / 23:53

1 answer

0

I do not have knowledge on query optimization, but I think one way to optimize it is to use indexes. Check what 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 this is done, try to execute the select using the reserved word EXPLAIN . This will display the index (s) being used. This way, you can act on this information by creating a new index or modifying it according to existing relationships.

I hope I have helped.

    
06.01.2018 / 03:51