Lack of composite indexes for meta tables

Question

Why wp_commentmeta table does not have a composite index (comment_id, meta_key) by default? I believe it's the only useful index for that table, am I wrong?

Instead, it has a strange set of indexes:

mysql> show create table wp3_commentmeta;
...
PRIMARY KEY  (`meta_id`),
KEY `comment_id` (`comment_id`),
KEY `meta_key` (`meta_key`)
...

The same thing about wp_postmeta.

Answer 1

A standard WordPress schema "sync" via dbDelta() will only add indexes, not drop them. Same goes for fields. We never touch the storage schema either, so it'd be the default for MySQL (which in latest versions is now InnoDB).

On the face, a comment_id_meta_key index makes perfect sense. But when you look at how WordPress actually uses its metadata tables, the use case is less obvious.

When you request a meta key for an object (post, comment, or user), we fetch all meta keys for that object, then cache them. This very important, as it only needs a comment_id index, not a comment_id_meta_key index.

Of course, that query would still use a comment_id_meta_key index just fine, but that index would be significantly larger, as rather than being a column of integers, you'd also be looking at a column of varchar(255). So it's less efficient for the primary use case of metadata.

There's limited API for the next example: a query to fetch all meta keys across all objects (probably to ascertain which objects have meta keys). This, again, is a good use case for a meta_key index, not a comment_id_meta_key index.

The other big API for metadata is querying against it with meta queries. These are not (and don't pretend to be) efficient. They query against meta_key (and sometimes meta_value) with the hope of returning object IDs for the main object query (comments inner or left joining commentmeta, typically). Again, comment_id_meta_key does not help here.

Scaling WordPress is a mixture of caching, query optimization, and yes, database optimization. If you find you are running queries that use these indexes, there's really nothing wrong with adding them. It's not much different than switching to InnoDB (which, yes, you should). I'm just not familiar with a core-prepared query that would miss the existing indexes but benefit from comment_id_meta_key.

Answer 2

You are not limited in this case. Just create the needed composite indexes and remove any duplicate indexes. For example:

ALTER TABLE wp3_commentmeta ADD INDEX comment_id_meta_key_ndx (comment_id, meta_key);
ALTER TABLE wp3_commentmeta DROP INDEX comment_id;

Just make sure your queries actually uses the newly made indexes.