Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
It's exactly what I did. But the answer is: why? –  Dmitriy Isaev Nov 7 '12 at 12:25
1  
Won't your manually created indexes be removed again after a WP update which applies dbDelta()? –  Geert Nov 7 '12 at 12:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.