Reading data, a database either uses an index to find rows or scans the table. A scan is not automatically a problem — on a small table it is the fastest thing available — but on a large one it is expensive, so the first question is how often it happens.

Over 23.3 hours of production traffic, our database read 150 million rows. Ninety million of those reads used no index at all.

no index   90,042,118   (60.0%)
indexed    60,043,308

That looks like something badly wrong, and some of it is. Much of it, once you see which tables are involved, is the database doing the right thing — so the work is separating the harmless scans from the expensive ones.

Where the number comes from

The same table that counts index usage has a row per table where the index name is NULL. That row is row-level I/O that went through no index: a full scan, or a lookup that fell back to one.

SELECT object_name, count_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = DATABASE() AND index_name IS NULL;

This counter tracks rows touched, not queries run. A single scan of a hundred-thousand-row table adds a hundred thousand to the total, so the raw number is meaningless without knowing the size of the table being scanned.

The column that decides whether a number is a problem

Here are the worst offenders by unindexed reads, with the row count beside them:

table                    no index    indexed     rows
analytics_hits         62,370,286  24,103,386  565,368
tools                   7,089,568   2,248,723      808
ai_directory_clicks     4,765,460      61,700   42,416
analytics_rum           3,224,545   2,052,323   45,470
outbound_clicks         2,930,112     852,952   50,758
tags                    2,901,422     224,407    3,985
analytics_daily         1,984,857     778,808   42,280
task_runs               1,971,103   1,159,115   56,028
analytics_engagement    1,277,330           1   38,734

Look at the second row. tools has seven million unindexed reads across 808 rows. That is not a missing index. A table of 808 rows fits in a page or two of memory, and the optimiser scans it because a scan is cheaper than descending an index and then fetching rows. Adding an index there would make writes slower and reads no faster.

The same reasoning covers tags at 3,985 rows. Small tables get scanned. That is the system working.

The three that are not fine

analytics_hits, 565,368 rows, the largest table in the schema, and 62 million of its 86 million reads used no index. Whatever is reading it is reading a lot of it, repeatedly.

ai_directory_clicks, 42,416 rows, with 4,765,460 unindexed reads against 61,700 indexed. That is 77 unindexed reads for every indexed one, on a table far too big to be scanning casually.

analytics_engagement, 38,734 rows, 1,277,330 unindexed reads — and its indexes were read once. Not once per hour. Once, in 23 hours. It has indexes; effectively nothing uses them.

Those three tables share a profile: large enough to make a scan expensive, with a ratio suggesting that scanning is the normal access path rather than the exception.

Whether a scan hurts depends on what fits in memory

A scan of a table already resident in the buffer pool runs at memory speed and costs almost nothing. A scan of one that does not fit reads from disk and, worse, evicts whatever else was in there. So what matters is which table is being scanned:

buffer pool                512.0 MB
whole schema               596.5 MB

analytics_hits             410.4 MB   (data 150.7 + index 259.7)
ai_directory_clicks         19.1 MB
analytics_engagement         8.5 MB
tools                        2.8 MB
tags                         0.5 MB

This settles the small tables completely. tools and tags are under three megabytes between them; they live in memory permanently and their millions of scanned rows cost essentially nothing. Those numbers can be confidently dismissed.

And it makes analytics_hits considerably worse than it first looked. At 410.4 MB against a 512 MB pool, one table is eighty percent of the cache. It cannot be scanned without pushing most of everything else out, so its unindexed reads are slow, and they also make every other query on the system slower by evicting other data from the cache. That cost appears nowhere near the query responsible.

Note also that the schema totals 596.5 MB against a 512 MB pool. The working set does not fit, so eviction is routine rather than exceptional, and the biggest scanner gets to decide what everyone else loses.

What this does not tell you

It does not tell you which query is doing it, and that is the next thing you need. This table aggregates by table, not by statement. The statement digests — performance_schema.events_statements_summary_by_digest — are where the responsible query lives, along with how often it ran and how many rows it examined per execution.

We did not read those. This guide identifies the tables worth investigating; it does not close any of the three cases it opens. One day of measurement supports a shortlist and not a diagnosis, so a shortlist is what this is.

The one thing to check first

Before hunting a query, check whether the scan is the optimiser's choice or its only option. Those are different problems with different fixes:

rows small, no index used     correct — leave it
rows large, no suitable index  add one, after finding the query
rows large, index exists but unused
                               the index does not match the query:
                               wrong column order, a function wrapped
                               around the column, or a type mismatch
                               between the column and the parameter

The third case is the frustrating one and the most common. An index on (a, b) cannot serve a query filtering on b alone. An index on a column disappears the moment you wrap it in a function — which is why converting a timestamp column inside the query makes a correct answer arrive slowly. And a string column compared against a number will convert every row rather than use the index, silently.

In all three, EXPLAIN on the real query with real parameters answers it in one step, and guessing answers it in an afternoon.

How this was measured, and what it leaves open

One production MySQL 8.0.46 schema over 23.3 hours carrying 768,115 queries, read from performance_schema with row counts joined from information_schema. Nothing was changed and no index was added.

The counters reset when the server restarts, and ours had been up 23.3 hours after a deploy. A longer window would change every figure here, and would be more trustworthy for exactly the reason the companion measurement needs one: infrequent jobs are invisible in a short one.

Row estimates are estimates. information_schema.tables.table_rows is sampled on InnoDB rather than counted, so the row column above is approximate. It is accurate enough to separate 808 from 565,368, which is all it is being asked to do.

Sixty percent describes this workload and nothing else. An application dominated by small lookup tables would score worse and be perfectly healthy. The figure is worth computing on your own system because there is no universal benchmark to compare it against, only the behaviour of your own tables and whether the big ones are being scanned.