MySQL doesn't use indexes in a SELECT clause subquery

Question

I have an "events" table

table events
  id (pk, auto inc, unsigned int)
  field1,
  field2,
  ...
  date DATETIME (indexed)

I am trying to analyse holes in the trafic (the moments where there is 0 event in a day)

I try this kind of request

SELECT
    e1.date AS date1,
    (
        SELECT date
        FROM events AS e2
        WHERE e2.date > e1.date
        LIMIT 1
    ) AS date2
FROM events AS e1
WHERE e1.date > NOW() -INTERVAL 10 DAY

It takes a very huge amount of time

Here is the explain

+----+--------------------+-------+-------+---------------------+---------------------+---------+------+----------+-------------+
| id | select_type        | table | type  | possible_keys       | key                 | key_len | ref  | rows     | Extra       |
+----+--------------------+-------+-------+---------------------+---------------------+---------+------+----------+-------------+
|  1 | PRIMARY            | t1    | range | DATE                | DATE                | 6       | NULL |        1 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | ALL   | DATE                | NULL                | NULL    | NULL | 58678524 | Using where |
+----+--------------------+-------+-------+---------------------+---------------------+---------+------+----------+-------------+
2 rows in set (0.00 sec)

Tested on MySQL 5.5

Why can't mysql use the DATE indexe? is it because of a subquery?

may be a typo **WHERE e1 > NOW() -INTERVAL 10 DAY**. should be this : `WHERE e1.date > NOW() -INTERVAL 10 DAY` — 1000111, Jun 01 '16 at 07:22
Would you please tell us what you are trying to do? May be restructuring the query might use index. — 1000111, Jun 01 '16 at 07:48
Is not that it is not using an index, but you sare making a subquery per row, approx 60 millions of subquery is a thing that you should avoid. — Roger Russel, Jun 02 '16 at 03:05
No it is not. I have a WHERE clause (e1.date > NOW() - INTERVAL 10 DAY) wich select only a small portion of the 60M rows. And trying to link 1 row in the subquery for each fetched row... should be approximatively the same that simply joining a table... — nemenems, Jun 02 '16 at 07:58
First, let's figure out what you are trying to do. The subquery can return _any_ date grater than `e1.date`. Perhaps you wanted the smallest? If so, add an `ORDER BY`. — Rick James, Jun 07 '16 at 04:39
Or, you are really just doing an `EXISTS` test? But I don't see a `HAVING`? — Rick James, Jun 07 '16 at 04:43

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

Your query suffers from the problem shown here which also presents a quick solution with temp tables. That is a mysql forum page, all of which I unearthed thru finding this Stackoverflow question.

You may find that the creation and populating such a new table on the fly yields bearable performance and is easy to implement with the range of datetimes now() less 10 days.

If you need assistance in crafting anything, let me know. I will see if I can help.

edited May 23 '17 at 11:58

Community

1
1

answered Jun 02 '16 at 02:44

Drew

24,851
10
43
78

Thx, but I understand that MySQL is still not ready for working with SUBQUERIES... I have to find an alternative way to analyse holes in trafic – nemenems Jun 02 '16 at 08:59
Well let me know if I can help. Not sure what holes in traffic means, unless you mean related to your event and next event issue (gaps) – Drew Jun 02 '16 at 13:01
Holes are gaps. I want to detect periods without trafic The other way of achieving this is to GROUP BY SUBSTR(date, 1, 16). I will have all minutes with trafic. And I will find with this query the gaps with an external script. – nemenems Jun 03 '16 at 12:09
I am happy to help. But i need sample data, and expected results, showin in tabular form – Drew Jun 03 '16 at 13:10

score 0 · Answer 2 · answered Jun 07 '16 at 04:46

0

You are looking for dates with no events?

First build a table Days with all possible dates (dy). This will give you the uneventful days:

SELECT dy
    FROM Days
    WHERE NOT EXISTS ( SELECT * FROM events
                        WHERE date >= days.day
                          AND date  < days.day + INTERVAL 1 DAY )
      AND dy > NOW() -INTERVAL 10 DAY

Please note that 5.6 has some optimizations in this general area.

answered Jun 07 '16 at 04:46

Rick James

135,179
13
127
222

this is the alternative I am going to apply... But it's disapointing that MySQL 5.5 doesn't handle this kind of subqueries. If I have more time, I will try to do a test againt 5.6 or 5.7 Thx for the help – nemenems Jun 07 '16 at 09:57

MySQL doesn't use indexes in a SELECT clause subquery

2 Answers2