Why Missing Database Indexes Are Usually the Real Reason a Site Is Slow
When a website feels sluggish, the reflexive assumption is often that the server needs more resources — more CPU, more RAM, faster storage. In a genuinely large share of real-world cases, particularly for database-driven sites that have grown over time, the actual root cause is something a hosting upgrade doesn't fix at all: a missing database index, causing the database engine to work far harder than necessary for a query that should be nearly instantaneous.
What an Index Actually Does
A database index is a separate, auxiliary data structure that allows the database engine to locate specific rows quickly, without needing to scan through every single row in a table to find the ones matching a given query's conditions — conceptually similar to a book's index, letting you jump directly to a relevant page rather than reading the entire book sequentially to find a specific topic. Without an appropriate index covering the columns a query filters or sorts by, the database has no choice but to perform a full table scan, examining every single row to determine whether it matches the query's criteria.
Why This Scales So Badly as Data Grows
The critical, often underappreciated detail is how dramatically the cost of a full table scan grows with table size: a query performing a full scan against a table with a thousand rows might complete in a couple of milliseconds, imperceptibly fast — the exact same query, and the exact same missing index, against a table that's grown to five million rows (an entirely plausible size for a mature e-commerce catalog, a busy forum's post history, or years of accumulated log data) can take seconds rather than milliseconds, since the database is now examining literally thousands of times more rows to answer the identical logical question.
Why This Explains the "It Used to Be Fast" Pattern
This scaling behavior explains a specific, common pattern that confuses many site owners: a site that ran perfectly fast for its first year or two, gradually becoming noticeably slower over subsequent years, with no obvious single change anyone can point to as the cause. The underlying missing index was there all along, from the very beginning — it just wasn't yet costly enough to notice at a smaller data volume, and its cost grew silently, proportionally with the table's row count, until it eventually crossed the threshold where the resulting delay became genuinely, noticeably slow to actual visitors.
Why a Hosting Upgrade Alone Doesn't Fix This
As discussed in the piece on disk I/O throttling elsewhere on this blog, moving an inefficiently-queried site to more powerful hosting — faster storage, more CPU — genuinely does help to some degree, since faster underlying hardware can churn through an unnecessarily large table scan somewhat more quickly than slower hardware could. But it doesn't fix the fundamental inefficiency: the query is still scanning far more data than necessary, and as the table continues growing, that same inefficient query will eventually become slow again on the new, more powerful hardware too, just at a somewhat larger row count than it did on the previous, less powerful setup — buying time, not solving the actual underlying problem.
How to Actually Identify Missing Indexes
MySQL and MariaDB provide a specific diagnostic tool built exactly for this purpose: the EXPLAIN command, run in front of any query, reveals exactly how the database engine plans to execute it — critically, whether it intends to use an existing index or fall back to a full table scan. A query plan showing "Using filesort" or a scan type indicating a full table examination, particularly against a table known to have a large row count, is a direct, actionable signal pointing at a missing index as the likely root cause of that specific query's slowness.
Why WordPress Sites With Many Plugins Are Particularly Prone to This
WordPress's flexible, plugin-extensible architecture means many plugins add their own custom database tables or query patterns, and not every plugin developer applies equally rigorous database optimization practices — a popular, otherwise well-regarded plugin can still ship with an inefficiently-indexed custom table that performs fine during the developer's own testing (typically against a small, sparse test dataset) but degrades significantly once a real site's data volume grows over months or years of actual production use, a gap between development-time testing conditions and real-world production scale that's a common, recurring source of this specific problem.
The Query Monitor Plugin as a Practical Starting Point
For WordPress specifically, the Query Monitor plugin (also mentioned in the pieces on I/O throttling and CPU resource limits elsewhere on this blog) provides an accessible, in-dashboard way to see exactly which database queries a given page load is executing and how long each one takes, without needing direct database access or command-line comfort — a practical, low-barrier starting point for identifying which specific queries are actually the slow ones worth investigating further with a more detailed EXPLAIN analysis.
The Takeaway
A missing database index is one of the most common, and most fixable, root causes of a website's performance gradually degrading over time as its data volume grows — and unlike many performance problems, it's not addressed by simply upgrading to more powerful hosting, since the underlying inefficiency scales right alongside the growing table regardless of the hardware underneath it. Identifying and adding the appropriate index, often a change taking a competent developer only minutes once correctly diagnosed, frequently produces a far more dramatic and durable performance improvement than a hosting upgrade alone ever would.
Tags: database indexes, MySQL performance, query optimization