I have an index on the created_at column on my actions table. When I run the following query
explain
select * from `actions` where `created_at` between '2022-01-18 06:00:00' and '2022-01-30 05:59:59'
I get the following which shows that the index is being used.
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | actions | NULL | range | actions_created_index | actions_created_index | 5 | NULL | 3775484 | 100.00 | Using index condition |
However, if I change the date to something like
explain
select * from `actions` where `created_at` between '2022-01-01 06:00:00' and '2022-01-30 05:59:59'
The output is now
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | actions | NULL | ALL | actions_created_index | NULL | NULL | NULL | 28446203 | 25.04 | Using where |
This query now does not use the created_at index. Is this an expected behavior? What can I do to ensure the created_at index is always being used regardless of the date range?
CodePudding user response：
MySQL uses a query optimizer to come up with a query plan. In your second query, it predicted that ignoring the b-tree index would be faster since the range was so large.
Also see this answer: https://dba.stackexchange.com/questions/48072/why-does-mysql-ignore-the-index-even-on-force-for-this-order-by
You can use
...
FORCE INDEX FOR ORDER BY created_at
but even this is only a hint to the optimizer. https://dev.mysql.com/doc/refman/8.0/en/index-hints.html