wp_postmeta has inefficient indexes. The published table (see Wikipedia) is
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,
PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
AUTO_INCREMENT provides no benefit; in fact it slows down most queries (because of having to look in secondary index to find auto_inc id, then looking in data for actual id you need)
AUTO_INCREMENT is extra clutter - both on disk and in cache.
- Much better is
PRIMARY KEY(post_id, meta_key) -- clustered, handles both parts of usual
BIGINT is overkill, but that can't be fixed without changing other tables.
VARCHAR(255) can be a problem in MySQL 5.6 with
utf8mb4; see workarounds below.
- When would meta_key or meta_value ever be
CREATE TABLE wp_postmeta (
post_id BIGINT UNSIGNED NOT NULL,
meta_key VARCHAR(255) NOT NULL,
meta_value LONGTEXT NOT NULL,
PRIMARY KEY(post_id, meta_key),
The typical usage:
JOIN wp_postmeta AS m ON p.id = m.post_id
WHERE m.meta_key = '...'
- The composite
PRIMARY KEY goes straight to the desired row, no digression through secondary index, nor search through multiple rows.
INDEX(meta_key) may or may not be useful, depending on what other queries you have.
- InnoDB is required for the 'clustering'.
- Going forward, use utf8mb4, not utf8. However, you should be consistent across all WP tables and in your connection parameters.
The error "max key length is 767", which can happen in MySQL 5.6 when trying to use CHARACTER SET utf8mb4. Do one of the following (each has a drawback) to avoid the error:
- Upgrade to 5.7.7 for 3072 byte limit -- your cloud may not provide this;
- Change 255 to 191 on the VARCHAR -- you lose any keys longer than 191 characters (unlikely?);
- ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese;
- Use a "prefix" index -- you lose some of the performance benefits;
- Reconfigure (if staying with 5.6.3 - 5.7.6) -- 4 things to change: Barracuda + innodb_file_per_table + innodb_large_prefix + dynamic or compressed.
meta_id is probably not used anywhere. (But it is a risk to remove it).
- You could keep
meta_id and get most of the benefits by changing to these indexes:
PRIMARY KEY(post_id, meta_key, meta_id), INDEX(meta_id), INDEX(meta_key, post_id). (Note: By having
meta_id on the end of the PK, it is possible for post_id+meta_key to be non-unique.)
- Changing from
BIGINT to a smaller datatype would involve changing other tables, too.
- Moving to 5.7 should not be incompatible.
- Shrinking to
VARCHAR(191) would require the user to understand that the limit is now the arbitrary "191" instead of the previous arbitrary limit of "255".
- The 'reconfigure' fix is DBA issues, not incompatibility issues.
I hope that some of what I advocate is on the WordPress roadmap. Meanwhile, stackoverflow and dba.stackexchange are cluttered with "why is WP running so slow". I believe that the fixes given here would cut back significantly in such complaint-type Questions.
Note that some users are changing to utf8mb4 in spite of compatibility issues. Then they get in trouble. I have tried to address all the MySQL issues they are having.
Taken from Rick James mysql blog: source