SQL command too slow

First of all, I'd like to introduce EXPLAIN , just put it before your query that MySQL shows several useful data to discover the bottleneck of your query.

Secondly , 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 this line you are making a CROSS JOIN , which is a Cartesian product , ie you are crossing ALL the lines of the three tables. This means that if your 3 tables have 1,000 rows, your result will have 1000 * 1000 * 1000 . This gives 1,000,000,000 rows, and that thousand rows for each table is quite easy to have in production.

So the first step would be to use the correct JOIN for your query. Since I believe you want all the information, I'll assume it's INNER JOIN . The conditions for the JOIN you already have in your WHERE clause, and I would already add ativo = 1 to JOIN , because there is no way to execute a JOIN on a record that will be filtered afterwards (could be applied no gol too, but without the DB structure I do not know what that field is). I'll assume that the ativo field refers to the funcionario table. Our query looks 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 date comparison (assuming the structure is a DATE or DATETIME ), I can think of 3 approaches to this:

data_venda like '2018-09%'

YEAR(data_venda) = 2018 AND MONTH(data_venda) = 9

EXTRACT(YEAR_MONTH FROM data_venda) = 201809

That said, I would not know which of the options above is the most performative, it would be necessary to search some benchmark or do some tests to be able to say something.

And the last thing ( not least ) would be to see if your tables have index and analyze if they need one.

To end 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

In this case, as I understand it, you need to separate your conditions for consultation with parentheses, separating their first conditions from their last condition, which is one date or another.

I put an asterisk in the parentheses that you need to change tell me if this is good.

EX

SELECT funcionario.nome, foto.foto, count(*) total
FROM venda, funcionario, foto
WHERE *(* venda.idfuncionario = funcionario.idfuncionario AND funcionario.idfuncionario = foto.idfuncionario AND gol = 1 AND ativo = 1 AND idsancall IN (5,7,8,42,2,3) *)* AND *(* data_venda like '2018-09%' OR backlog_data like '2018-09%' *)*
GROUP BY nome,foto
ORDER BY total DESC

Friend, good afternoon!

Can you tell if these tables have many records? If so, why do not you index these columns. Second point like the degrade much performance use something like this:

SELECT funcionario.nome, foto.foto, count(*) total
FROM venda, funcionario, foto
WHERE venda.idfuncionario = funcionario.idfuncionario AND funcionario.idfuncionario = foto.idfuncionario AND gol = 1 
   AND ativo = 1 AND idsancall IN (5,7,8,42,2,3) 
  AND ( (MONTHNAME(data_venda) = 9 and YEAR (data_venda) = 2018)
OR (MONTHNAME(backlog_data ) = 9 and YEAR (backlog_data ) = 2018) ) 
GROUP BY nome,foto
ORDER BY total DESC

In addition to the above changes do not forget the relatives that the friend mentioned, because you use or and that makes all the difference.

I edited the code in the question and I believe it was easier to understand. As it stands, there are several and and one or , so you're probably having loop infinity in the query. Try using the code like this:

SELECT funcionario.nome, foto.foto, count(*) total
FROM venda, funcionario, foto
WHERE venda.idfuncionario = funcionario.idfuncionario
  AND funcionario.idfuncionario = foto.idfuncionario
  AND gol = 1
  AND ativo = 1
  AND idsancall IN (5,7,8,42,2,3)
  (AND data_venda like '2018-09%' OR backlog_data like '2018-09%')
GROUP BY nome,foto
ORDER BY total DESC

Being (AND data_venda like '2018-09%' OR backlog_data like '2018-09%') so (in parentheses) you will return all the previous conditions + date_venda of the month or backlog_data of the month, instead of the original form, which it brought all the ands or backlog_data of the month.