Skip to content
Why MySQL Chooses a Full Table Scan Over a Primary Key Index and How to Fix It
Databases 4 min read

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

The article explains why MySQL 8.x may ignore a primary‑key index and perform a full table scan for an IN subquery, describes the optimizer’s cost‑model reasoning, and shows how rewriting the query with an INNER JOIN restores index usage and dramatically improves performance.

IT Services Circle
IT Services Circle
IT Services Circle

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.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

SQLQuery OptimizationMySQLindexesDatabase Performance
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.