MySQL date range query indexing is working for limited range only

Question

EXPLAIN EXTENDED 
SELECT * FROM table_name
where dummy_date 
between '2020-12-01' AND '2020-12-31'
-- between '2020-12-01' AND '2020-12-20'
AND name='something';
ALTER TABLE `table_name` ADD INDEX `dummy_date_index`(`dummy_date`);

if dummy_date is between 20-25 days then query used indexing for 700,000 records but if it is more than 25 days then query is not using indexing and doing full table scan. please let me know the correct approach to utilize index.

Answer 1

INDEX(name, dummy_date)

would be better.

It is right for the Optimizer to avoid an index that is not very selective. There is overhead in using an index. There is also overhead in skipping rows during a table scan. The Optimizer has to pick which is the "lesser of two evils"; sometimes it does not pick the right 'lesser'.