WP-Specific Indexing
WordPress stores a lot of plugin/theme data in meta tables (wp_postmeta, wp_usermeta) and options (wp_options). This is flexible, but it can become slow when large tables are queried without the right indexes. The goal is not to add lots of indexes "just in case" - it's to add targeted indexes that match the slow queries you actually see.
- Use the slow query log to find the offenders.
- Add one index at a time and verify with
EXPLAIN. - Remember: indexes speed up reads but add write overhead and consume memory.
The Meta Table Bottleneck
A standard WordPress table ships with primary indexes designed specifically for basic blog functionality. As the database scales into thousands of WooCommerce orders, the limitations of these default indexes become mathematically critical.
The Default Architecture
| Target Table | Default Indexing Limitation | Operational Consequence |
|---|---|---|
wp_postmeta | Indexed only on post_id and meta_key individually. | Queries searching for a specific post_id and a specific meta_key together frequently trigger a full scan because the database lacks a combined map. |
wp_options | Indexed strictly on option_name (Unique). | The core WordPress bootstrap sequence explicitly queries WHERE autoload = 'yes'. The database has absolutely zero index for the autoload column, forcing it to scan the entire table on every uncached page load. |
ALTER TABLE operations can be expensive on large tables. Always take a backup first, and run schema changes during a maintenance window (or use an online schema change tool).
Deploying Custom Indexes
You can alter the schema to add indexes. Back up first and measure impact.
1. The Critical Postmeta Composite Index
This single index completely resolves the vast majority of WooCommerce and Advanced Custom Fields (ACF) performance queries when filtering products or extracting deep metadata arrays.
-- Creates a combined map locking the ID directly to the Meta Key
ALTER TABLE wp_postmeta ADD INDEX idx_postid_metakey (post_id, meta_key);
2. The Autoload Options Index
This forces the database to rapidly isolate strictly the core options required to boot the WordPress framework.
-- Indexes the specific column tracking boot priority
ALTER TABLE wp_options ADD INDEX idx_autoload (autoload);
-- Optional: For heavy multi-site topologies, bind the Option Name identically
ALTER TABLE wp_options ADD INDEX idx_autoload_optionname (autoload, option_name);
3. The Prefix Meta Value Index
Some plugins query meta_value directly. Indexing meta_value is expensive because it is a large text column, but in specific cases a prefix index can help.
-- Indexes only the first 20 characters to strictly save RAM while providing massive acceleration
ALTER TABLE wp_postmeta ADD INDEX idx_meta_value (meta_value(20));
Validation & Verification
Do not blindly deploy indexes without subsequently proving the MySQL query optimizer actually utilizes them.
Use the EXPLAIN Clause
Run the exact slow query identified by your logs, prefacing it with the EXPLAIN operator.
EXPLAIN SELECT * FROM wp_postmeta WHERE post_id = 14502 AND meta_key = '_price';
Expected Analysis Outcome: Look for the key column in the output. If it prominently displays idx_postid_metakey, the query successfully utilized the newly created roadmap. If it displays NULL, the optimizer rejected the index and proceeded with a full scan.
Common Mistakes & Troubleshooting
| Configuration Failure | Operational Symptom | Remediation Protocol |
|---|---|---|
| Index bloat | Disk usage and write overhead increase. | Add only indexes you can justify with slow logs and EXPLAIN. Drop redundant indexes if safe. |
| Table lock / long running alter | Timeouts and slow site during schema changes. | Run changes during maintenance or use an online schema change tool. |
| Index disappears after migration | Performance regresses after migrations/restores. | Validate indexes after migrations and re-apply if needed. |