Database Optimization for Your Own Site: Tuning Slow Query Logs, Indexes, and Table Structures
1. Why WordPress databases get slow
WordPress uses MySQL as its database. The default table structure works fine for a small site, but as content grows it gets slower and slower. The root cause isn’t server configuration but two design problems: the wp_postmeta table stores all custom fields in a single key-value table, so looking up one product’s specs requires multiple JOINs; and the wp_options table mixes all configuration items together, so every admin page load has to scan the whole table.
An average WordPress page queries the database 12 to 20 times, most of which are queries against wp_postmeta. When you install 10 plugins and each one writes custom fields to wp_postmeta, this table can balloon to hundreds of thousands of rows. At that point, any page request with a meta_query has to scan through hundreds of thousands of rows, and slow queries are inevitable.
| Common sources of slow queries | Table involved | Typical symptom | Share |
|---|---|---|---|
| Filtering posts by custom fields | wp_postmeta | Product filter page takes over 3 seconds to load | About 40% |
| Loading plugin settings in the admin | wp_options | Admin shows a blank screen for a few seconds after every save | About 20% |
| Tag/category archive pages | wp_term_relationships | Archive pages slow down when there are many posts | About 15% |
| Comment and user table queries | wp_comments/wp_users | Comment lists or user management are slow | About 10% |
| Complex queries from plugins themselves | Plugin-created tables | One particular feature page is especially slow | About 15% |
Once the database is slow, page caching can mask part of it. When the page cache hits, the database isn’t queried at all. But when the cache misses (right after publishing a post, or visiting an uncached page), slow queries are exposed. So database optimization and caching strategy need to be considered together, see Three-Layer Caching Strategy for Your Own Site. If the database is already slow enough to affect Core Web Vitals, see for optimization priorities Core Web Vitals Optimization.
2. Slow query logs: how to find slow SQL
The first step to optimizing a database isn’t adding indexes — it’s finding out which SQL query is actually slow. You can’t guess this by eye: the query you think is slow is often a different one. MySQL has a built-in slow query log. Turn it on and it records every query that exceeds the threshold.
Add three lines to your MySQL config (my.cnf or my.ini): slow_query_log=1, long_query_time=1, slow_query_log_file=/var/log/mysql/slow.log. This means: enable the slow log, record queries taking longer than 1 second, and write the log to the specified file. After running for a day, use mysqldumpslow or pt-query-digest to aggregate the log by query type and find the TOP 10 slowest queries.
| Steps | Actions | Tool / Command | Purpose |
|---|---|---|---|
| 1. Enable the slow log | Edit my.cnf, add three lines | Restart MySQL to apply | Log slow queries |
| 2. Run for a day | Browse the site normally for a day | — | Collect real slow queries |
| 3. Aggregate and analyze | Aggregate by SQL fingerprint | mysqldumpslow -s t | Find the TOP 10 |
| 4. EXPLAIN each one | Check the execution plan | EXPLAIN the SQL statement | See if it uses an index |
| 5. Retest after optimizing | Compare timing before and after | SHOW PROFILE or timing | Confirm the improvement |
Example: you notice the product filter page is very slow. You open the slow log and see the TOP 1 slow query is a SQL statement with a meta_query filtering a custom field in the wp_postmeta table. EXPLAIN shows type is ALL (full table scan) and rows is 120,000 — this query scans the entire wp_postmeta table every time. Now you know to add an index on the meta_key field, not to upgrade the server.
Don’t leave the slow query log on long-term — it has its own IO overhead. Run it for a day to collect data, then turn it off, or raise the threshold to 2 seconds to only log the slowest queries. For day-to-day monitoring, use a database performance monitoring tool (such as Prometheus + Grafana), see Site Monitoring and Alerting Checklist.
3. Index optimization: which fields need indexes
The two most important columns in EXPLAIN output are type and rows. If type shows ALL, the query is doing a full table scan and must be indexed; if it shows ref or eq_ref, it’s already using an index and doesn’t need changes. The rows column shows how many rows MySQL estimates it will scan — the bigger the number, the more dangerous. Scanning under 10,000 rows is fine; scanning over 100,000 rows needs optimization.
The table most worth indexing in a WordPress database is wp_postmeta. By default this table only has an index on post_id, but real queries often filter by meta_key and meta_value. Add a composite index on (meta_key, meta_value) and queries filtering by custom fields can be over 10x faster. Note it’s a composite index, not a separate index on meta_key — with only meta_key indexed, queries that also filter by meta_value still have to go back to the table.
| Table | Query scenario | Recommended index | Results |
|---|---|---|---|
| wp_postmeta | Filter by meta_key + meta_value | (meta_key, meta_value(191)) | Over 10x faster |
| wp_postmeta | Look up meta by post_id | post_id index already exists | No change needed |
| wp_options | Look up config by option_name | option_name index already exists | No change needed |
| wp_comments | Query comments by post_id | comment_post_ID index already exists | No change needed |
| wp_posts | Query by post_type + post_status | Composite index already exists | No change needed |
| Plugin-created tables | Filter by date/status | Check EXPLAIN to decide | Add as needed |
Counterintuitive take: most people see a slow query and add an index right away, but indexes aren’t free. Every index takes up disk space, and every INSERT or UPDATE forces MySQL to update all of them. On a write-heavy, read-light table like wp_options (written every time you save settings in the admin), piling on indexes actually slows writes down. The right approach: add indexes freely on read-heavy, write-light tables (wp_postmeta, wp_posts); be cautious with write-heavy, read-light tables (wp_options, wp_comments) and skip the index if you can.
Always test in staging before adding an index. Running ALTER TABLE on the production database can lock a large table for minutes and take your site down with a 502. For production, use an online DDL tool like pt-online-schema-change or gh-ost so the table isn’t locked. For crater’s maintenance experience on overseas site databases, see crater’s tech stack and four in-house plugins.
4. Table structure optimization: the pitfalls of wp_postmeta and wp_options
Some problems can’t be solved with indexes; you have to change the table structure. The key-value design of wp_postmeta is one of them: it stores all custom fields in a single table, so pulling the specs for one product takes several JOINs. Once you have thousands of products and dozens of custom fields, the table balloons to hundreds of thousands of rows and index optimization hits its ceiling.
At that point you have two options: replace postmeta with a custom table, putting the frequently queried custom fields in their own table keyed by field type so queries hit the right table directly; or use WordPress’s wp_options autoload mechanism, marking read-heavy, write-light settings as autoloaded so you don’t query the options table every time. For a B2B site, option one suits sites with complex product specs, and option two suits sites with lots of plugins installed.
| Issue | Symptoms | What to optimize | Risk |
|---|---|---|---|
| wp_postmeta bloat | Filter pages slow, table over 100k rows | Build a custom table for high-frequency fields | Requires code changes and testing |
| Too much wp_options autoload | Admin slow, autoload over 1MB | Clean up settings from unused plugins | Back up before deleting |
| Too many wp_posts drafts/revisions | Post list slow, junk revisions piling up | Clean up auto revisions and drafts | Confirm before deleting |
| wp_commentmeta bloat | Comment list slow | Turn off auto-saving of spam comments | Minor impact |
| Large wp_users table | User management slow | Clean up inactive users | Be careful not to delete the wrong one |
Example: an industrial equipment website has 50 products, each with 20 custom parameters (material, power, dimensions, precision, etc.). All of these live in wp_postmeta, so loading a single product page means joining wp_postmeta 20 times. The right approach is to create a custom table, wp_product_specs, and store all 20 parameters as columns in one row. Then fetching product specs is a single SELECT with no joins. It doesn’t take much development work, but the performance gain is an order of magnitude.
Back up your database before changing any table structure. For backup and restore drills, see Backup and recovery drills for your site. If your database has already been hacked, see the cleanup and hardening process in What to do after your site is hacked.
5. Verifying and maintaining the optimized setup
After adding an index or changing a table structure, you can’t just call it done. You have to do three things. First, re-run the slow SQL from before the optimization with EXPLAIN and confirm the type column changed from ALL to ref or eq_ref. Second, compare the actual query time before and after to confirm the slow query really got faster. Third, watch for a week to see whether any new slow queries show up in the slow query log. Optimization often just moves the bottleneck from one place to another.
| What to check | How to verify | Pass criteria | What to do if it fails |
|---|---|---|---|
| EXPLAIN execution plan | Re-run EXPLAIN on the slow SQL | type is no longer ALL, rows dropped by an order of magnitude | Index isn’t being used, check the column types |
| Actual query time | Time the SQL directly | Down from over 1 second to under 100 milliseconds | Wrong index, analyze again |
| Slow query log | Check the log after 3 days | The original slow query is gone or much less frequent | New slow queries appeared, keep optimizing |
| Write performance | Time INSERT/UPDATE | Writes aren’t noticeably slower | Too many indexes, drop the redundant ones |
| Front-end page time | Lighthouse score | LCP on a cache miss<1.5秒 | There are other bottlenecks |
Database optimization isn’t a one-time job. As content grows and plugins and themes get updated, new slow queries appear. Run a slow query log analysis once a month to see whether the top 10 has changed. Database optimization is a standing item in routine maintenance, see Six Monthly Tasks for Site Maintenance. For the overall framework for choosing a tech stack for your own site, see crater Tech Stack. For how PHP version upgrades and database configuration work together, see Website maintenance guide.
FAQ
How do I enable the MySQL slow query log in WordPress?
Why is the wp_postmeta table so slow?
Will adding indexes slow down my site?
How do I read EXPLAIN output?
Will adding an index lock the table in production?
How often should I do database optimization?
Final thoughts
The biggest trap in database optimization is upgrading the server the moment things slow down. Going from 1 core/2GB to 4 cores/8GB costs double, and the slow queries are still slow, because the bottleneck isn’t CPU, it’s a full table scan with no index. The right order is always: turn on the slow log to find which specific SQL is slow, use EXPLAIN to check the execution plan, and only then decide whether to add an index or change the table structure.
Three steps to fix it. First, turn on the slow query log and let it run for a day, then use mysqldumpslow to find the top 10. Second, run EXPLAIN on each one: add an index wherever type=ALL, and for wp_postmeta start with a composite index on (meta_key, meta_value). Third, retest after optimizing and confirm the slow queries drop from 1 second to under 100 milliseconds. Once you’ve done these three steps, you’ll find the server specs haven’t changed at all, yet pages load several times faster.
One line worth remembering: when the database is slow, check the slow log first, don’t upgrade the server first. 80% of slow queries are full table scans that skip the index, and adding the right index beats upgrading your specs by a tier.