Database Indexes: Speed Up WordPress Without New Hosting

When WordPress is slow, the first thought is caching or hosting. But very often the culprit is a database doing too much work for each query. Picture a wp_postmeta table with 800,000 rows and a query that asks for every product with a certain tag. Without an index, MySQL must read all 800,000 rows one by one — this is a "full table scan" and it takes seconds. With the right index, the same query returns in a few milliseconds.

What an index is and why it helps

An index is like the index at the back of a book. Instead of flipping through every page for a term, you check the index, which points you straight to the right page. In a database, an index is a separate structure (usually a B-tree) that keeps the values of one or more columns sorted, with a pointer to the row. When a query filters on that column (WHERE), the database uses the index and skips reading the rest of the table.

Where WordPress usually hurts

The wp_postmeta table is the most common bottleneck. It stores everything — from SEO descriptions to WooCommerce product data — and easily grows to hundreds of thousands of rows. Default WordPress has an index on meta_key, but many queries filter on a combination of meta_key and meta_value. Similarly, wp_options suffers when autoloaded options pile up, and on large sites wp_posts has slow queries on post_status and post_type.

How to find slow queries

Do not guess — measure. A few ways:

• The Query Monitor plugin shows every query on a page, sorted by duration, and highlights the slow ones.
• The MySQL slow query log records any query longer than a set threshold (e.g. 1 second).
• The EXPLAIN command in front of a query shows whether the database uses an index or runs a full scan (a "type" column of ALL is a warning).

How to add an index

A composite index on wp_postmeta that covers both the key and the start of the value looks like this:

ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key(191), meta_value(100));

For the wp_options table, an index on the autoload column helps because WordPress loads all autoload options on every page. If you are unsure, plugins such as Index WP MySQL For Speed add tested, safe indexes to key tables with one click and measure the result before and after.

Careful: an index is not free

Every index speeds up reads but slows down writes, because the database must update the index on each INSERT/UPDATE. So do not index everything — index the columns you actually filter or sort on. For a read-heavy site (a blog, a brochure site) indexes are almost pure gain. For a write-heavy site (a busy shop), balance it.

Regular database maintenance

Indexes work best on a clean database. Delete old post revisions, expired transients, spam comments and leftover data from removed plugins. A smaller table means a smaller index and faster queries. This goes hand in hand with optimizing the database itself.

Frequently asked questions

Will adding an index break anything? It does not change your data, only adds a structure for faster lookups. Still, on a large production table make a backup before ALTER TABLE, as the operation can briefly lock the table.

Does an index replace caching? No, it complements it. A cache avoids repeating a query, while an index speeds the query up when it must run. The fastest sites use both.

On our WordPress hosting plans the database runs on an NVMe drive, so even unoptimized queries are faster — and with good indexes the difference is dramatic. See our plans and pricing.