不带索引列表的USE INDEX()有何作用？为何能加速MySQL查询？

Great question! It might seem counterintuitive at first—why would telling MySQL to skip all indexes make a query run faster? Let's break down the likely reasons based on your query and how MySQL's optimizer works:

1. The optimizer picked a bad index (and you stopped it) #

MySQL's query optimizer tries to choose the "best" index based on table statistics, but it doesn't always get it right. For example:

• If your r table had an index on a field the optimizer thought would help with the IN (...) clause, but that index wasn't a covering index (meaning it didn't include all the fields you needed: name, distance, pk), MySQL would have to do a lot of "bookmark lookups" (going back to the main table to fetch missing data after finding rows via the index).
• If the IN list returned a large percentage of rows from the r table (say, 30% or more), traversing the index and doing all those lookups is way slower than just scanning the entire table once to grab all necessary data.

2. You forced a better join strategy #

When you disable indexes on r, MySQL might switch from a nested-loop join (which works well with small result sets from indexes) to a hash join. Hash joins are often more efficient for larger datasets, especially when joining multiple tables like your query does (with LEFT JOIN rs, INNER JOIN srr, etc.). The original index-driven plan might have chugged through small chunks of data repeatedly, while a hash join can process larger batches more efficiently.

3. Outdated statistics tricked the optimizer #

If the table statistics for r were stale (e.g., you've added/updated a lot of rows since the last stats refresh), the optimizer's calculations about index usefulness would be wrong. For example, it might think an index has high selectivity (few rows per key) when most rows actually match the IN condition. Skipping indexes forces the optimizer to use a full table scan, which aligns better with the actual data distribution.

4. Index overhead was worse than scanning #

Indexes aren't free—they add overhead for lookups, especially with multiple joins. If your query used an index that required frequent context switching between the index and the main table, eliminating that overhead via a full scan could save significant time.

A concrete example for your query #

Your IN clause pulls route_fk values from st.srr where srfk=3. If that subquery returns hundreds or thousands of r.pk values, using an index to find each of those rows in r and then fetching fields like name and distance via bookmark lookups is far slower than scanning all of r once, filtering matching pk rows, and proceeding with joins.

In short: USE INDEX() didn't speed up your query because it skipped indexes—it did so because it prevented MySQL from using an inefficient index strategy that was worse than a full table scan for your specific data and query pattern.

内容的提问来源于stack exchange，提问作者joshua miller