First I’d like to introduce EXPLAIN
, just put it before your query that Mysql shows various useful data to discover the bottleneck of your query.
Secondarily, if you show us the structure of your database our answers will be more accurate and it will be easier to help you.
But come on, seeing your query the first thing I noticed was the line:
FROM venda, funcionario, foto
On that line you’re making one CROSS JOIN
, which is a cartesian product, that is, you are crossing ALL rows of the three tables. That means that if your 3 tables have 1,000 rows, your result will have 1000 * 1000 * 1000
. That’s 1,000,000,000 lines, and that’s a thousand lines for each table is very easy to have in production.
So the first step would be to use the JOIN
correct for your query. As I believe you want all the information, I will assume that is the INNER JOIN
. The conditions for JOIN you already have in your clause WHERE
, and I would add the ativo = 1
in the JOIN
, because there is no reason to execute a JOIN
in a record that will be filtered after (could be applied to gol
also, but without the BD structure I do not know what this field is). I will assume that the field ativo
refers to the table funcionario
. Our query would look like this:
SELECT
fu.nome,
fo.foto,
count(*) as total
FROM venda as v
INNER JOIN funcionario as fu
ON v.idfuncionario = fu.idfuncionario AND fu.ativo = 1
INNER JOIN foto as fo
ON fu.idfuncionario = fo.idfuncionario
Another thing I would change would be the comparison of date (assuming the structure is a DATE
or DATETIME
), I can think of 3 approaches to this:
The one you’re already using, compare dates as strings:
data_venda like '2018-09%'
Using the functions YEAR
and MONTH
:
YEAR(data_venda) = 2018 AND MONTH(data_venda) = 9
Using the function EXTRACT
and taking the year and the month at the same time:
EXTRACT(YEAR_MONTH FROM data_venda) = 201809
That being said, I wouldn’t know which of the above options is the most performative, it would be necessary to research some benchmark or do some tests to be able to affirm something.
And the last thing (and not least) would you see if your tables have indexes and analyze whether they need a.
To finish your code would look like this:
SELECT
fu.nome,
fo.foto,
count(*) as total
FROM venda as v
INNER JOIN funcionario as fu
ON v.idfuncionario = fu.idfuncionario AND fu.ativo = 1
INNER JOIN foto as fo
ON fu.idfuncionario = fo.idfuncionario
WHERE gol = 1
AND idsancall IN (5, 7, 8, 42, 2, 3)
AND (
EXTRACT(YEAR_MONTH FROM data_venda) = 201809
OR EXTRACT(YEAR_MONTH FROM backlog_data = 201809
)
GROUP BY fu.nome, fo.foto
ORDER BY total DESC
try to include some image in your question, or a detailed explanation, of your bank’s structure
– Gabriel Gonçalves
Please put the structure of your table, without this it is difficult to help.
– Roberto de Campos
Thank you very much, excellent reply!
– Guilherme Santana