0
SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo

I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind, dateFrom, dateTo and a combination of dateFrom and dateTo.

But nothing changed, with or without indexes the query time is the same. I know BETWEEN doesn't use indexes, is it true?

As M Khalid Junaid suggested, this is the EXPLAIN PLAN of the above query:

+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+
| id | select_type | table        | type | possible_keys                  | key  | key_len | ref  | rows  | Extra                                          |
+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+
|  1 | SIMPLE      | table1       | ALL  | dateToFind                     | NULL | NULL    | NULL | 32027 |                                                |
|  1 | SIMPLE      | table2       | ALL  | dateFrom,dateTo,dateFrom_dateTo| NULL | NULL    | NULL |  6951 | Range checked for each record (index map: 0xE) |
+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+

Any suggestion?

3
  • 1
    Can you post the EXPLAIN plan for the above query ? Aug 27, 2014 at 11:56
  • @MKhalidJunaid thx i just added the explain plan
    – Daniele
    Aug 27, 2014 at 13:30
  • For starters don't use BETWEEN use >= it's much clearer that way.
    – Zane
    Aug 27, 2014 at 14:47

1 Answer 1

-1

In your query, the join has no conditions. Shouldnt be something like SELECT xxx FROM table1 JOIN table2 ON table1.column = table2.column and xxx.date between xxx to xxx .

Add: Here is the explain for the test table

explain select find from table1 t1 join table2 t2 on t1.find between t2.from1 and t2.to1;
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | t2    | index | from1,to1,from_to | from_to | 12      | NULL |    2 | Using index                                    |
|  1 | SIMPLE      | t1    | ALL   | idx               | NULL    | NULL    | NULL |    5 | Range checked for each record (index map: 0x1) |
2
  • 1
    dateToFind BETWEEN dateFrom AND dateTo is a condition. Aug 27, 2014 at 15:07
  • aha, so dateToFind is a column in table1 and dateFrom and dateTo are columns in table2. I have created a dummy table in my local to test , and it seems it will use the dateFrom_dateTo index, and for table1 is full table scan, which make sense because it needs to go through all the rows. Aug 27, 2014 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.