Speed up a MySQL BETWEEN query

Question

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?

• M Khalid Junaid: Can you post the EXPLAIN plan for the above query ?
• Daniele: @MKhalidJunaid thx i just added the explain plan
• Zane: For starters don't use BETWEEN use >= it's much clearer that way.

Answer 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) |

• ypercubeᵀᴹ: dateToFind BETWEEN dateFrom AND dateTo is a condition.
• cassiopeia88: 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.