MySQL indexes explained with WordPress query optimisation examples

Home » Coding » MySQL indexes explained with WordPress query optimisation examples
March 15, 2022 Coding, MySQL
MySQL indexes explained with WordPress query optimisation examples

Every time WordPress runs a query against your database MySQL has to find matching rows. Without an index it reads every row in the table from top to bottom — a full table scan that becomes painfully slow as your data grows. An index is a separate data structure, similar to the index at the back of a book, that lets MySQL jump directly to the rows that satisfy a WHERE, ORDER BY, or JOIN condition. WordPress ships with indexes on all its core tables (the post_status + post_type composite index on wp_posts is a good example), but the moment you add a custom table or run meta queries using WP_Query with a meta_query argument, you are likely introducing unindexed lookups. The EXPLAIN statement is the primary tool for diagnosing missing indexes: it shows you whether MySQL is doing a full scan (type: ALL) or using a key (type: ref or range). This article covers the three most common index types in MySQL — B-tree, composite, and covering indexes — with concrete examples drawn from WordPress table structures. You will see how to add an index to the wp_postmeta table to speed up meta_key + meta_value lookups, and how to write a composite index that eliminates a filesort for ordered meta queries. Keep in mind that indexes consume disk space and slow down INSERT/UPDATE operations, so index only the columns you actually query. Use the EXPLAIN guide first to confirm a scan is happening before adding any index.

Problem: A meta_key + meta_value lookup on wp_postmeta is performing a full table scan and slowing down a high-traffic WordPress page, and you need to add the right index to fix it.

Solution: Use EXPLAIN to confirm the full scan, then add a composite index on (meta_key, meta_value) and verify the improvement:

-- 1. Check existing indexes on wp_postmeta
SHOW INDEX FROM wp_postmeta;

-- 2. EXPLAIN a slow meta_key + meta_value lookup
EXPLAIN
SELECT post_id
FROM   wp_postmeta
WHERE  meta_key   = '_featured_product'
AND    meta_value = '1';
-- If type = 'ALL' a full table scan is happening

-- 3. Add a composite index on (meta_key, meta_value)
ALTER TABLE wp_postmeta
  ADD INDEX idx_key_value (meta_key, meta_value(20));
-- meta_value is a longtext so we index a 20-char prefix

-- 4. Verify: EXPLAIN should now show type = 'ref'
EXPLAIN
SELECT post_id
FROM   wp_postmeta
WHERE  meta_key   = '_featured_product'
AND    meta_value = '1';

-- 5. Index for a custom table that stores user events
CREATE TABLE IF NOT EXISTS wp_user_events (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(50)     NOT NULL,
    created_at DATETIME        NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_user_event (user_id, event_type),
    INDEX idx_created    (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Covering index: MySQL can answer the query from the index alone
-- (no lookup into the main table row)
ALTER TABLE wp_posts
  ADD INDEX idx_status_type_date (post_status, post_type, post_date);

EXPLAIN
SELECT ID, post_title, post_date
FROM   wp_posts
WHERE  post_status = 'publish'
AND    post_type   = 'post'
ORDER  BY post_date DESC
LIMIT  10;
-- With the covering index: Extra = 'Using index'

NOTE: The wp_postmeta table already has a meta_key index in WordPress core, but it does not include meta_value. Adding a composite index as shown above is safe on existing data — MySQL builds the index online and does not lock the table for reads. On very large sites (millions of rows) run the ALTER TABLE during a maintenance window or use pt-online-schema-change from Percona Toolkit to avoid query latency spikes. Also review the transactions guide to understand how index updates interact with transactional operations.

Related posts