CODEWITHSUNDEEP
mysqloptimizationindexingsubquery
nemenems
nemenems

Reputation: 1092

MySQL doesn't use indexes in a SELECT clause subquery

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?

Upvotes: 0

Views: 495

Answers (2)

Rick James
Rick James

Reputation: 142306

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.

Upvotes: 0

Drew
Drew

Reputation: 24959

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.

Upvotes: 1

Related Questions