在MySQL中，"USE INDEX"无法正常工作，但"FORCE Index"似乎可以正常工作 [英] In MySQL 'USE INDEX' is not working But 'FORCE Index' seems working fine

问题描述

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

当我执行此查询时，索引编制不起作用，但是如果我使用 force index ，则其可与索引一起使用.请让我知道将索引用于数百万行的正确方法.

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.

推荐答案

甚至比使用更好的索引，而不必理会USE或FORCE:

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

INDEX(name, dummy_date)

无论如何，如果大多数表都在该月中，那么使用 INDEX(dummy_date)的效率要比扫描表低.

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

这篇关于在MySQL中，"USE INDEX"无法正常工作，但"FORCE Index"似乎可以正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！