0
EXPLAIN EXTENDED 

SELECT * FROM table_name

--  FORCE INDEX(dummy_date)

 USE INDEX(dummy_date)

where dummy_date 

between '2020-12-01' AND '2020-12-31'

AND name='something';

ALTER TABLE `table_name` ADD INDEX `dummy_date_index`(`dummy_date`)

When I execute this query then indexing is not working but if I use force index then its working with index. Please let me know the correct approach to use the index for millions of rows.

ashutosh kumar
  • 45
  • 3
  • MySQL uses index by date only when the range is below 10 days. USE INDEX won't help. If you are sure that index usage will improve then force it. – Akina Feb 02 '21 at 10:12

1 Answers1

0

Even better than MySQL not using indexes with WHERE IN clause? --

Use a better index and don't bother with USE or FORCE:

INDEX(name, dummy_date)

Anyway, if most of the table is in that month, then using INDEX(dummy_date) is less efficient than scanning the table.

Rick James
  • 135,179
  • 13
  • 127
  • 222