JeffTheKiller JeffTheKiller - 1 month ago 10
MySQL Question

MySQL: Why my query can't finish?

I don't know why the query I've wroten, doesn't give me any output and just can't finish. Here http://sqlfiddle.com/#!9/8656d2/1 is the sample of my database, in real I have there about 260k records(rows). So you can see that query works with that table in the link, but in my whole database something is wrong. I waited almost 30 minutes for any results, but the query process is interminably. I don't know what should I do now, what can be the reason of described problem?

Answer Source

I don't actually know why the query isn't completing on your MySQL with 260K records. But I can speculate that the correlated subquery you have in the SELECT statement is the culprit. Let's look closely at that guy:

SELECT DATE_SUB(MAX(EVENT_TIME), INTERVAL 12 HOUR)
FROM my_table mt
WHERE
    EVENT_TYPE = '2' AND
    mt.ID = my_table.ID

You are basically telling MySQL to do a MAX() calculation across the entire table, for every record in your my_table table. Note that because the subquery is correlated, it might have to be run fresh for literally all 260K records. Hopefully you can see that 260K x 260K operations would be a bit slow.

If I be correct, then a possible fix would be to rephrase your query using a join to a subquery table which finds the max event times for each ID in your table. This query would be run once, and only once, and then it would be up to MySQL to find an efficient way to join back to your original table. But in any case, this approach should be eons faster than what you were using.

SELECT t1.*
FROM my_table t1
INNER JOIN
(
    SELECT ID, DATE_SUB(MAX(EVENT_TIME), INTERVAL 12 HOUR) AS max_event_time
    FROM my_table
    WHERE EVENT_TYPE = '2'
    GROUP BY ID
) t2
    ON t1.ID = t2.ID AND
       t1.EVENT_TIME > t2.max_event_time
WHERE t1.EVENT_TYPE != 3
ORDER BY t1.ID

Here is link to your updated Fiddle:

Demo