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
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
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