Mysql – Speed up a MySQL BETWEEN query

join;MySQLperformancequery-performance

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?

Best Answer

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) |
Related Question