Why MySQL Chooses a Full Table Scan Over a Primary Key Index and How to Fix It

A VIP user reported a MySQL 8.x query that selects rows from table A using an IN subquery, expecting the primary‑key index to be used, but the execution plan showed a full table scan of millions of rows.

The MySQL optimizer builds a cost model based on estimated I/O and CPU costs; because the size of the IN list produced by the subquery is uncertain, the optimizer conservatively chooses a full scan, which can be cheaper in its estimation.

Even forcing the primary key with force index(PRIMARY) did not change the plan, confirming that the optimizer’s cost estimates overrode the hint.

The recommended fix is to replace the IN subquery with an INNER JOIN. The rewritten SQL is:

select *
from A a
inner join (
  select max(id) as id
  from A
  where task_id in (1,2,3)
  group by task_id
) b on a.id = b.id;

After the change, the EXPLAIN output shows the primary‑key index being used and the query completes in seconds, scanning only a few hundred rows.

The article concludes that for large tables, queries containing IN with subqueries should be rewritten as joins to ensure index usage and avoid costly full scans that can block the database.