Very slow SQL command

Question

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

I’m trying to get him back on record backlog case to data_venda is not equal to the current month, when I put only data_venda like '2018-09%' works, but when I put data_venda like '2018-09%' OR backlog_data like '2018-09%' just keeps loading without returning errors.

Answer 1

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:

1. The one you’re already using, compare dates as strings:

   data_venda like '2018-09%'
   

2. Using the functions YEAR and MONTH:

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

3. 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

Answer 2

In this case I understand you need to separate your conditions for parenthesis consultation, separating your first conditions from your last condition which is a date or another.

I put asterisk in the parentheses that you need to change say if so was 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

Answer 3

Buddy, good afternoon!

Can you tell if these tables have a lot of records? If so, why Oce does not index these columns. Second point the like degrades a lot of 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

Besides the above changes do not forget the parentheses that the friend mentioned, because you use or and this makes all the difference.

Answer 4

I edited the code in the question and I believe it became easier to understand. As it stands, there are several and and a or, then you’re probably having loop infinite in query. Try to use 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

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