Your WordPress site feels slow. You’ve already compressed images, minified CSS, enabled object caching—and page load times are still terrible. The culprit is often hiding in plain sight: database table indexes that are missing, bloated, or broken.
Most solo operators never look at their database structure until something breaks. But queries that scan hundreds of thousands of rows without an index can add seconds to page generation time, even on small sites. Here’s how to find the problem tables and decide what to do about them.
What indexes do and why they break
An index is a sorted lookup table that helps MySQL find rows without scanning the entire table. When WordPress runs a query like SELECT * FROM wp_posts WHERE post_status = 'publish' ORDER BY post_date DESC, MySQL checks for an index on post_status and post_date. If those indexes exist, the query returns in milliseconds. Without them, MySQL reads every row in the table—thousands or millions of disk operations.
Indexes break or go missing for a few reasons:
- Plugin migrations that create custom tables without proper indexes
- Large DELETE operations that leave fragmented indexes behind
- Manual SQL imports that skip index definitions
- Composite indexes that don’t match the query’s WHERE clauses
The WordPress core tables—wp_posts, wp_postmeta, wp_options, wp_comments—ship with solid default indexes (WordPress Codex database schema reference, checked October 2026). But plugins that add metadata, log tables, or custom post types often don’t add indexes for their own queries.
How to identify slow queries and missing indexes
Start by enabling MySQL’s slow query log. Most managed WordPress hosts (including BigScoots) expose this in their dashboard or phpMyAdmin. If you run your own server, add these lines to my.cnf or my.ini:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
Any query taking longer than one second will be logged. Restart MySQL, let your site run under normal traffic for an hour, then open the log. Look for queries with high Rows_examined counts—anything above 10,000 rows for a single SELECT is a red flag.
Next, check which tables are missing indexes. Log into phpMyAdmin or your database client, select your WordPress database, and run:
SHOW TABLE STATUS;
Look at the Data_length and Index_length columns. If a table has 50 MB of data but only 2 MB of indexes, and you know plugins are querying it heavily, you likely need to add indexes.
To see which indexes exist on a specific table, run:
SHOW INDEX FROM wp_postmeta;
WordPress adds a post_id index and a meta_key index by default. If a plugin queries WHERE meta_key = 'custom_field' AND meta_value = 'something', and there’s no composite index on (meta_key, meta_value), that query will be slow.
When to add an index and when to leave it alone
Adding an index speeds up reads but slows down writes. Every INSERT, UPDATE, or DELETE has to rebuild the affected indexes. For tables that change constantly—like wp_options with autoloaded data—a poorly chosen index can make things worse.
Add an index if:
- The slow query log shows repeated queries scanning tens of thousands of rows
- The table is read-heavy (e.g., custom post type archives, metadata lookups)
- The query filters or sorts on a column that isn’t indexed
Skip the index if:
- The table has fewer than 1,000 rows (the overhead isn’t worth it)
- The column has very low cardinality (e.g., a boolean field with only two values)
- Write performance matters more than read performance (e.g., logging tables)
To add a single-column index:
ALTER TABLE wp_postmeta ADD INDEX meta_value_idx (meta_value(191));
The (191) limits the indexed portion of the column to 191 characters, which keeps the index size manageable for utf8mb4 collation (MySQL CREATE INDEX documentation, checked October 2026).
For composite indexes matching a specific query pattern:
ALTER TABLE wp_postmeta ADD INDEX meta_key_value_idx (meta_key, meta_value(191));
The non-obvious tip: rebuild fragmented indexes quarterly
Even well-indexed tables degrade over time. If you run a membership site, a course platform, or any WordPress install with heavy comment or metadata churn, indexes become fragmented. MySQL has to read more disk blocks to traverse the index tree, and queries slow down even though the index exists.
Check fragmentation by running:
SHOW TABLE STATUS LIKE 'wp_postmeta';
Look at the Data_free column. If it’s more than 20% of Data_length, the table is fragmented. Rebuild it with:
OPTIMIZE TABLE wp_postmeta;
This locks the table briefly (usually seconds for tables under 1 GB), so run it during low-traffic windows. Set a calendar reminder to OPTIMIZE your largest tables every quarter. On a site with 50,000 posts and heavy metadata, this alone can cut query times in half.
Want more infrastructure deep-dives like this? Subscribe to One Two Three Send—every article is written for operators who want to understand the layer below the dashboard.