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?
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: