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