Postmeta, Custom Tables and Indexes

A plugin stores a delivery estimate against every product with update_post_meta(), and the report that reads it orders the rows by that estimate. It works on a development site with forty products. In production, with 32,000 posts and 172,007 postmeta rows, the query the report runs takes 238 ms, and the report runs it more than once per page.

Nothing about the plugin is wrong. update_post_meta() is the correct API for per-object data, the query is written the way the template tags suggest, and the number of rows is what the business asked for. What is wrong is the shape: a value stored in a long text column, sorted in a table whose indexes do not contain that column.

This lesson measures that failure, then fixes it with a custom table and an index, then installs the table with dbDelta(), which has its own rules about how a schema has to be written. The measured numbers come from WordPress 7.0.7 on PHP 8.5.6 and MySQL 9.6.0, on the dataset above, and every figure is from this install rather than from a vendor claim.

wp_postmeta is three columns and two narrow indexes

On a stock WordPress 7.0.7 install, the parts of the table definition that matter are these:

CREATE TABLE wp_postmeta (
  meta_id bigint(20) unsigned NOT NULL auto_increment,
  post_id bigint(20) unsigned NOT NULL default '0',
  meta_key varchar(255) default NULL,
  meta_value longtext,
  PRIMARY KEY  (meta_id),
  KEY post_id (post_id),
  KEY meta_key (meta_key(191))
) ENGINE=InnoDB;

Three columns of substance and one index on each of the two you are allowed to filter by efficiently. meta_value is a LONGTEXT and appears in no index, and that is not an oversight. An index on a text column needs a prefix length to be practical, and a prefix index on serialised plugin data helps one plugin while making every insert in the table more expensive.

What that means in practice depends on the direction the query travels. Give the optimiser a post_id and you get an index lookup plus a few row reads. Give it a meta_key and you get the same on the key index. Ask it to sort by the value, and there is nothing to walk.

ORDER BY meta_value_num becomes an arithmetic expression MySQL cannot index

Here is the shape that shows up in plugin code, written the way a plugin author would write it:

$query = new WP_Query( array(
    'post_type'      => 'product',
    'posts_per_page' => 24,
    'meta_key'       => '_delivery_estimate_days',
    'orderby'        => 'meta_value_num',
    'order'          => 'ASC',
) );

WordPress turns that into a statement. I captured it on the lab install with the posts_request filter, so this is the real SQL, not a reconstruction:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE ( wp_postmeta.meta_key = '_delivery_estimate_days' )
  AND wp_posts.post_type = 'product'
  AND wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 ASC
LIMIT 0, 24;

orderby => 'meta_value_num' is the line to notice. WordPress implements it as meta_value+0, an arithmetic expression, which is how you make MySQL sort a text column numerically. It also guarantees that no index on meta_value could be used for the sort even if one existed, because the sort key is an expression over the column rather than the column. The GROUP BY wp_posts.ID and the SQL_CALC_FOUND_ROWS come along for the ride for the same reason they did in the previous lesson.

Here is the EXPLAIN, traditional format, on 172,007 postmeta rows:

+----+-------------+-------------+------+---------------+----------+---------+-------+-------+----------------------------------------------+
| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows  | Extra                                        |
+----+-------------+-------------+------+---------------+----------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | wp_postmeta | ref  | post_id,      | meta_key | 767     | const | 51928 | Using where; Using temporary; Using filesort |
|    |             |             |      | meta_key      |          |         |       |       |                                              |
|  1 | SIMPLE      | wp_posts    | ref  | PRIMARY,      | PRIMARY  | 8       | ...   |     1 | Using where                                  |
+----+-------------+-------------+------+---------------+----------+---------+-------+-------+----------------------------------------------+

Read the Extra column on the first row. Using filesort is the cost of the ORDER BY, and Using temporary is the deduplication for the GROUP BY. The rows estimate is 51,928, which is the optimiser's guess at how many meta rows match the key. MySQL 9.6's own EXPLAIN ANALYZE on the same statement reports what actually happened: 32,001 rows were materialised into a temporary table, then sorted, and the limit was applied afterwards.

-> Limit: 24 row(s) (actual time=238..238 rows=24 loops=1)
    -> Sort row IDs: `(wp_postmeta.meta_value + 0)`, limit input to 24 row(s) per chunk
        -> Table scan on <temporary> (actual time=220..237 rows=32001 loops=1)
            -> Temporary table with deduplication (actual time=220..220 rows=32001 loops=1)
                -> Nested loop inner join (actual time=0.0467..92.8 rows=32001 loops=1)
                    -> Index lookup on wp_postmeta using meta_key (actual time=0.0299..37.8 rows=32001 loops=1)

Warm buffer pool, three runs with SQL_CALC_FOUND_ROWS: 260 ms, 240 ms, 236 ms. Without it, five runs: 215 ms, 198 ms, 206 ms, 220 ms, 201 ms. The sort dominates both numbers, and it scales with the number of rows carrying that meta key, which is the number of products.

A custom table pays when the query filters or sorts on the value

The important detail from that EXPLAIN is not the filesort. It is that the index MySQL chose, meta_key, is useless for the sort, while post_id, which would at least narrow the range if the query filtered by product, is not usable because the query filters by type and status instead. A metadata table makes the object-keyed read cheap and the value-keyed read expensive, and no amount of tuning changes that.

So move the value into a column. Here is the same data in a table with a real schema, installed with dbDelta():

function wpsi_install_estimates_table() {
    global $wpdb;

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    $table   = $wpdb->prefix . 'delivery_estimates';
    $collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        post_id bigint(20) unsigned NOT NULL,
        carrier varchar(32) NOT NULL,
        estimate_days smallint(5) unsigned NOT NULL,
        estimated_at datetime NOT NULL,
        PRIMARY KEY  (id),
        KEY days_post (estimate_days, post_id),
        KEY carrier_days (carrier, estimate_days)
    ) {$collate};";

    dbDelta( $sql );
}
add_action( 'plugins_loaded', 'wpsi_install_estimates_table' );

The read becomes a prepared statement against the columns the query actually uses, and the join to wp_posts stays because you still need the publish status and the post type:

$table = $wpdb->prefix . 'delivery_estimates';

$rows = $wpdb->get_results( $wpdb->prepare(
    "SELECT p.ID, e.estimate_days, e.carrier
     FROM {$table} e
     INNER JOIN {$wpdb->posts} p ON p.ID = e.post_id
     WHERE p.post_type = %s AND p.post_status = %s
     ORDER BY e.estimate_days ASC
     LIMIT 24",
    'product',
    'publish'
) );

Here is the before and after on the same data, same machine and same server settings. The custom table was built by the dbDelta() call above and backfilled with an INSERT ... SELECT from wp_postmeta, and both queries were run with a warm buffer pool. The postmeta row is the statement from the previous section. The custom table row is the query above, selecting p.ID, e.estimate_days and e.carrier.

	Postmeta	Custom table
Index used for the sort	meta_key, then a temporary table	days_post, walked in order
Extra in EXPLAIN	Using temporary; Using filesort	NULL (no filesort)
Rows the plan reports	51,928 estimated, 32,001 actual	48 estimated, 24 read before the limit stopped the scan
Median of three warm runs	240 ms	0.73 ms

Two orders of magnitude, from the same data, because one query asks MySQL to finish sorting everything and the other walks an index that is already in the required order and stops after 24 rows. The exact ratio depends on the dataset and it is not the point. The filesort line disappearing from the plan is the point.

Column order in a composite index is not a style choice

The index in the example is days_post (estimate_days, post_id), not (post_id, estimate_days). The reason is the rule you can apply to any composite index: equality first, then range, then sort.

estimate_days comes first because the query orders by it and does not constrain it with an equality, so making it the leading column puts the sort order in the index and lets the limit stop early. If the query also filtered with estimate_days = 5 or a range like estimate_days <= 10, the leading equality or range would narrow the scan and the remaining columns would supply the sort.

I built the wrong version on purpose to show what breaks. This table has KEY post_days (post_id, estimate_days), and the queries below run against it:

-- 1. Equality on post_id, sort on estimate_days. Both are served by the index.
SELECT post_id FROM wp_lab_bad
WHERE post_id = 42 AND estimate_days <= 10
ORDER BY estimate_days LIMIT 24;

-- 2. Range on estimate_days, sort on estimate_days. The index leads with post_id.
SELECT post_id FROM wp_lab_bad
WHERE estimate_days <= 10
ORDER BY estimate_days LIMIT 24;

-- 3. Range on estimate_days, sort on post_id. The sort column leads the index.
SELECT post_id FROM wp_lab_bad
WHERE estimate_days <= 10
ORDER BY post_id LIMIT 24;

Query 1: type=range  key=post_days  rows=1     Extra: Using where; Using index
Query 2: type=index  key=post_days  rows=32001 Extra: Using where; Using index; Using filesort
Query 3: type=index  key=post_days  rows=24    Extra: Using where; Using index

Query 2 is the one to study. The index is used, the rows estimate is the whole table, and a filesort appears, because estimate_days is not a leading prefix of the index and MySQL has to read and sort. Query 3 touches 24 rows with no filesort, because it sorts by the column the index already leads with. Same index, same data, different question, different answer.

The rule in one table: put the columns the query pins with equality first, then the column it ranges over, then the column it sorts by, and remember that the sort has to be on a leading prefix for the index to supply the order.

dbDelta reformats your schema, and it never drops anything

dbDelta() parses the CREATE TABLE statement you give it, compares it against the live table, and generates the ALTER TABLE statements needed to reconcile them. It is not a schema migration tool and it has rules.

It wants one field per line. The parser splits the body of the statement on newlines and reads each line as one field or one index definition. Formatting inside a line is forgiving: it normalises whitespace, accepts KEY, INDEX, UNIQUE KEY and PRIMARY KEY in any case, and lowercases index names before comparing them. I ran the same schema through dbDelta() three times in four formats, including one where the whole table body sat on a single line, and none of them produced a duplicate index. What did go wrong was putting two index declarations on one line:

-- The parser reads this as one index over sku and id.
KEY lookup (sku, id), PRIMARY KEY  (id)

That line holds two index definitions, and dbDelta() treated them as one index over sku and id instead of two separate indexes. The newline is the delimiter, so a line holds one field or one index and nothing else. Write the schema the way SHOW CREATE TABLE prints it, one definition per line, and this stops being a category of bug you have to think about.

The constraint that catches people is the other one. dbDelta() adds and modifies, and it does not remove. I installed a table with a margin column, then removed that column from the schema and ran the function again:

after first install: id,sku,margin
after removing margin from the schema: id,sku,margin

The column is still there. Nothing warns you. If you need to drop a column or an index, you write the ALTER TABLE ... DROP yourself, behind a schema version option so it runs once.

Which is the real shape of using this function: keep a version option, compare it on an upgrade hook, run dbDelta() for the additive changes, and handle the destructive ones explicitly.

function wpsi_maybe_upgrade_estimates_table() {
    $installed = (int) get_option( 'wpsi_estimates_schema', 0 );

    if ( $installed >= 2 ) {
        return;
    }

    wpsi_install_estimates_table();   // the CREATE TABLE from above, run through dbDelta()

    if ( $installed >= 1 ) {
        // Only for sites that had version 1, and only once.
        global $wpdb;
        $wpdb->query( "ALTER TABLE {$wpdb->prefix}delivery_estimates DROP COLUMN legacy_zone" );
    }

    update_option( 'wpsi_estimates_schema', 2, false );
}
add_action( 'plugins_loaded', 'wpsi_maybe_upgrade_estimates_table' );

Call it on plugins_loaded or an activation hook, never on init for every request. A dbDelta() call is a SHOW COLUMNS and an SHOW INDEX per table, and running it on every page view adds two queries to every request to answer a question whose answer changed last month.

A custom table is a maintenance contract

The performance case for a table like this one is strong and it is not the whole decision. Core maintains wp_postmeta: it ships the schema, upgrades it, handles multisite, and the table exists when your plugin is activated on a site you have never seen. The moment you write CREATE TABLE, all of that becomes yours.

Concretely, you now own the schema version option above, an uninstall routine that drops the table only if the site owner asked for data removal, a per-site install loop for network activation, and the backfill that copies existing rows out of wp_postmeta in batches rather than in a loop over update_post_meta(). You also own the fact that nothing else in WordPress knows your table exists: no revision copy, no REST field, no export, no trash, and no cascade when a product is deleted.

So the test is not "is postmeta slow". It is "does any read of this data filter, sort or group by the value". If the answer is no, if every read starts from a known post id, postmeta is the right home and this lesson does not apply. If the answer is yes and the row count is large, the custom table is the correct answer rather than a rewrite, and the only question left is whether you are willing to own it.

Show the solution and the reasoning

For the delivery-estimate query in this lesson the access pattern is: no equality on the estimate, a sort by estimate_days, and a filter supplied by the join to wp_posts rather than by the new table. That puts estimate_days first, and post_id second to make the index cover the join.

The result to look for in the after-EXPLAIN is the absence of Using filesort and a rows estimate near the limit rather than near the table size. In this lesson's data that was 48 estimated and 24 actually read, against 32,001 materialised for the postmeta version, and the median run went from 240 ms to 0.73 ms. If your after-EXPLAIN still shows a filesort, the usual causes are an index whose leading column is not the sort column, or a WHERE clause on the joined table that MySQL resolves after the sort.

Reversing the index columns gives you the wrong-version table from earlier in the lesson. With (post_id, estimate_days), a query that ranges over estimate_days and sorts by it reads every index entry and filesorts, because the range column is not a leading prefix. A query that ranges over estimate_days and sorts by post_id is fine, because the sort column is the leading one. That is what the rule means in practice: the index serves the sort only when the sort is on a leading prefix of the columns already fixed by equality.

Two things are easy to get wrong in the move itself. Backfill with a batched INSERT ... SELECT rather than a loop over update_post_meta(), or you will run hundreds of thousands of statements and hold the table while you do it. And keep the read path behind a single accessor function, because the storage decision is one you may want to reverse, and reversing it is cheap when one function knows which table the data is in.

What a custom table does not fix is the row growth. If the plugin writes a row per variation per day, the new table has the same growth curve as the old one, and it will need pruning. A schema change buys you a query shape. It does not buy you a data retention policy, and you will need one.