Custom Tables and the $wpdb API in Practice

Creating the table is the easy part. Fifty lines of SQL in an activation callback, a dbDelta() call, and it exists. What arrives afterwards is what gets plugins into trouble: the second migration, the site built by importing a database from somewhere else, the multisite network where your table exists on one site out of forty, and the uninstall that leaves orphaned rows because nobody wrote the cleanup.

Adding a table is a decision to run a small piece of infrastructure. That is sometimes the right call. It is a bad call when you make it because a meta_query felt clumsy, and a very bad call when you make it with a hard-coded wp_ prefix.

postmeta stops working when the query, not the data, is the problem

wp_postmeta is a key-value store with a composite index on (post_id, meta_key) and a separate index on meta_key. For a single post's metadata that is close to ideal: one indexed lookup returns everything attached to that post.

The trouble starts when you filter across posts, and it comes from the schema. A query for "posts where the rating is at least 4 and the source is field-notes, ordered by rating" needs one join per meta key, and because values are stored as text, the numeric comparison becomes CAST(meta_value AS SIGNED), which no index can serve. The ordering then forces a temporary table and a sort on top of that.

-- What a two-key meta_query with an ordering sends to MySQL.
SELECT SQL_CALC_FOUND_ROWS wp_posts.*
  FROM wp_posts
  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )
 WHERE 1=1
   AND ( wp_postmeta.meta_key = 'wpsifero_rating'
     AND ( ( mt1.meta_key = 'wpsifero_rating' AND CAST(mt1.meta_value AS SIGNED) >= '4' )
       AND ( mt2.meta_key = 'wpsifero_source' AND mt2.meta_value = 'field-notes' ) ) )
   AND ( wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish' )
 GROUP BY wp_posts.ID
 ORDER BY wp_postmeta.meta_value+0 DESC
 LIMIT 0, 10

On a test install with 20,000 posts and 40,000 meta rows, that query ran at a mean of 19.4 ms over fifty runs. The equivalent selection against a purpose-built table with a composite index ran at 3.7 ms, and both returned one row. The plans show why: the meta version ends in a sort of a materialised temporary table, while the table version does a range scan on (source, rating) and builds nothing.

Those numbers come from one machine and a synthetic dataset, so do not carry them to a production estimate. The structural point generalises: a schema that stores every value as text and every attribute as a row serves a single-record read beautifully and a cross-record filter badly. Stay with postmeta while your access pattern is per-post and the volume is modest. Move when you are filtering, sorting, aggregating or counting across records, or when one plugin's rows have grown enough to compete with everything else in a shared table.

dbDelta is a parser, and it only notices two kinds of change

dbDelta() compares the CREATE TABLE statement you pass against the table as it exists, and issues the ALTER TABLE statements needed to close the gap. What it closes is narrower than people assume: it adds missing columns and it changes column types. It does not drop a column or an index that is absent from your statement.

initial:            id, keep_me, drop_me      indexes: PRIMARY, keep_idx, drop_idx
dbDelta reported:   Changed type of wp_wps_drop.keep_me from varchar(32) to varchar(64)
after:              id, keep_me, drop_me      indexes: PRIMARY, keep_idx, drop_idx

I ran that on a real 7.0.7 install: a column and an index were removed from the statement, the type change was applied, and nothing was dropped. This is how a schema that has been through several versions accumulates stray columns. Removing one is a deliberate ALTER TABLE ... DROP COLUMN behind your own version check, and only worth writing when no older code is still reading the column.

The formatting rules come from the same place, because dbDelta() reads the statement as lines of text.

• One field or key definition per line. Everything inside the parentheses is split on \n, so a field and a key on the same line are treated as one field.
• A space after PRIMARY KEY before the parenthesis. PRIMARY KEY (id) and PRIMARY KEY (id) both work, PRIMARY KEY(id) does not.
• No IF NOT EXISTS. The table name is extracted with CREATE TABLE ([^ ]*), so CREATE TABLE IF NOT EXISTS wp_notes yields a table name of IF, and every run after the first reports Created table IF while the real table goes untouched.
• Bare column names in the examples, with no backticks, even though the parser strips them in most positions.

The spacing rule is the most repeated and least explained, so here is the mechanism. The parser takes the text before the first space in a line as the field name. PRIMARY KEY (id) yields PRIMARY, which fails the field-name check and is handed to the index parser as KEY (id), and that parses. Remove the space and KEY(id) is not something the index parser accepts, so the run emits undefined array key warnings from inside upgrade.php and a broken ALTER TABLE ... ADD `` (``). Two spaces is convention; one space is the requirement.

Identifiers need escaping too, and %i is not a whitelist

Every mention of $wpdb->prepare() talks about values. Values are half of an injection surface. A table or column name that comes from configuration, a filter or a request is an identifier, you cannot put quotes around it, and %s is the wrong placeholder for it.

WordPress 6.2 added %i for exactly this. It wraps the argument in backticks, doubles any backtick inside it, and passes it as an identifier.

global $wpdb;

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

$wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
// SELECT COUNT(*) FROM `wp_wpsifero_ratings`

$wpdb->get_results( $wpdb->prepare(
    'SELECT * FROM %i WHERE rating >= %d AND source = %s',
    $table,
    4,
    'field-notes'
) );

What it does not do is validate. Feed it a name that does not exist and you get a database error at query time, nothing earlier. Here are two versions of the same mistake, run against the install I have been using:

$input = 'wp_posts WHERE 1=1 -- ';

$wpdb->prepare( 'SELECT COUNT(*) FROM %i', $input );
  -> SELECT COUNT(*) FROM `wp_posts WHERE 1=1 -- `
  -> WordPress database error: Incorrect table name 'wp_posts WHERE 1=1 -- '

"SELECT COUNT(*) FROM {$input}"
  -> 58

The prepared version fails closed: the whole string becomes one quoted identifier, so there is no table with that name. The interpolated version executes and returns the row count of wp_posts, having swallowed the rest of the clause in a SQL comment. That is escaping versus validation, stated as output. If an identifier can be influenced by a user, resolve it against a fixed list of names your plugin knows and use the value from the list. %i is for identifiers you have already decided are legitimate.

One more detail, because it costs people hours: %s escapes and adds its own quotes, so writing WHERE x = '%s' is harmless and you will never notice core repairing it. And prepare() refuses an argument used as both an identifier and a value in the same query, which is core catching a category error for you.

$wpdb->prefix is the table name, and wp_ is only its default

WordPress does not require the tables to be called wp_. The starter wp-config.php defines $table_prefix = 'wp_', some hosts change it, and multisite appends the site ID. On a network where the main site uses wp_, the second site's tables are wp_2_posts and wp_2_options, while the shared wp_users and wp_usermeta stay unnumbered.

$wpdb->prefix is the prefix of the site currently being served. $wpdb->base_prefix is the prefix of the network's main site, which is what a network-wide table needs, because it has to exist exactly once. If your SQL string starts with wp_, none of this reaches you, and the plugin works on the developer's machine and fails on the first multisite install or the first site with a custom prefix.

global $wpdb;

$per_site = $wpdb->prefix . 'wpsifero_ratings';       // one table per site
$shared   = $wpdb->base_prefix . 'wpsifero_index';    // one table for the network

$wpdb->get_blog_prefix();                             // e.g. 'wp_2_'
$wpdb->get_blog_prefix( 1 );                          // e.g. 'wp_'
$wpdb->base_prefix;                                   // e.g. 'wp_'

The second reason to build the name from $wpdb instead of a constant is that switch_to_blog() changes the value. A maintenance routine that loops over the network and switches before running its SQL picks up each site's prefix automatically. One that captured the prefix once at the top of the function writes every site's rows into the first site's table.

Design the index around the query you actually run

An index is a bet about access patterns, so write the query first and then decide what to index. The shape to aim for on a filter you run often is a composite index whose leading column is the equality condition and whose second column is the range or sort condition, because MySQL can use both parts of it and skip the sort.

-- Query: rating >= 4 AND source = 'field-notes', ordered by rating.
KEY source_rating (source, rating)

-- Plan with that index:
--   -> Sort: r.rating DESC, r.post_id
--       -> Index range scan on r using source_rating
--            over (source = 'field-notes' AND 4 <= rating)

Two habits worth having. Give the table a single-column primary key on an AUTO_INCREMENT column, because updates and deletes against a table with no primary key get painful at scale. And put a KEY on the column you join or filter by most often, which for most plugin tables is a foreign key to a post, a user or an order. An index you never use costs write time and disk. An index you need and do not have costs a table scan on every request.

You now own migrations, uninstall and the rollout

Everything WordPress normally handles for its own tables becomes your job, and the failures are quiet.

Migrations first. Store a schema version in an option and compare it on a hook, as lesson 3 established. What is new with a table is the second half: dbDelta() adds the column, and your routine has to backfill existing rows with something sensible. Write the backfill so it can run twice, because a request that dies halfway through an upgrade leaves the version option unwritten and the next request tries again.

Then uninstall. The table outlives the plugin unless you drop it, and dropping it is irreversible. Sites do get restored from backups and owners do delete plugins by accident, so the choice worth making deliberately is whether cleanup is the default or the exception. Plenty of plugins ask, with an option or a filter, and delete nothing unless the answer is explicit.

Then multisite, which is the one that catches people late. Decide whether the table is per site or per network before you write the first query, because changing your mind later means moving data between prefixes on a live network. Per site is the safer default for user content; per network suits a shared index. Whichever you pick, activation has to cover the sites that already exist and site creation has to cover the ones that come later, and those are two different code paths.

What none of this buys you is an argument for adding a table early. The cost shows up eighteen months in, when you are running an upgrade routine across forty sites at 2 a.m. Allocate the table when the query pattern demands it, and then treat the schema as a product with a release cycle rather than a detail of the feature that needed it.

IF NOT EXISTS              table created, dbDelta reported "Created table IF", second run repeats it
field and key on one line  second field never parsed; type and default changes are missed
PRIMARY KEY(id)            PHP warnings from upgrade.php, then ALTER TABLE ... ADD `` (``

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

global $wpdb;

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

dbDelta( "CREATE TABLE {$table} (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    post_id bigint(20) unsigned NOT NULL,
    rating tinyint(3) unsigned NOT NULL DEFAULT 0,
    source varchar(32) NOT NULL DEFAULT '',
    created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY  (id),
    KEY post_rating (post_id, rating),
    KEY source_rating (source, rating)
) {$collate};" );

// After dbDelta() has added the column, in the same upgrade routine.
$wpdb->query( $wpdb->prepare(
    'UPDATE %i SET context = %s WHERE context = %s',
    $table,
    'unknown',
    ''
) );