This article describes the high-performance keys we use in the Index WP MySQL For Speed plugin. Experience on large sites, especially those with the WooCommerce and BuddyPress plugin systems, teaches us that better keys help site performance a lot. This article presumes some knowledge of SQL database systems.
The plugin’s mission is to add keys to handle as many slow WordPress database situations as possible. Misquoting Leo Tolstoy, “All fast databases are alike; each slow database is slow in its own way.” That means we need your help to understand your performance problem. If the plugin doesn’t help some part of your site perform well, please let us know! Add a comment here or use the plugin’s support forum on WordPress.org.
The plugin puts high-performance keys on the WordPress tables mentioned here. In this article we name them with the default prefix. For example, the comments
table we name wp_comments
. But, it’s possible for your tables to have other prefixes. The plugin handles them correctly.
Keys and storage engine versions
The kinds of keys we can put on your WordPress tables depends on the version of MariaDB or MySQL your site uses. Specifically, it depends on the variant of the InnoDB storage engine built into the server software. Two variants are out there in the wild. The older is called Antelope, and it can only place prefix keys on the first 191 characters of any database column containing VARCHAR
text. The newer is called Barracuda. Its limit is 768 characters. Most WordPress text columns are defined as VARCHAR(250)
. That means Antelope servers must have the 191 character prefix keys, but Barracuda servers don’t need prefix keys at all on those columns.
WordPress’s database columns containing content are defined as LONGTEXT
. Those LONGTEXT
columns, if they need keys at all, always need prefix keys: they have a maximum length in the gigabyte range.
We add high-performance keys to both Antelope and Barracuda-based database tables, but on Barracuda they’re still more efficient than they are on Antelope. We show you both sets of high-performance keys in this article. A means Antelope, B means Barracuda.
For more information about prefix keys please read this.
Upgrading your database server software
If your site’s database uses Antelope, the plugin shows you this notice.

Upgrading MySQL is a job for your hosting provider or system administrator. You should ask them to do it if they still use an older, Antelope, version of the MySQL or MariaDB database software. (But don’t bother if you use GoDaddy’s hosting service. Thousands of their customers have asked for the upgrade, and as of early 2024 they don’t show any signs of doing it.)
Compound primary keys
In some tables we change the primary key so it is a multicolumn, compound, key. Such a primary key makes many table lookups (WHERE clauses in queries) faster. Primary keys serve two purposes. The first and obvious one is giving each row of the table a unique identifier. InnoDB uses so-called clustered indexes, meaning that the data of the table is stored in the primary key structure. So, the second purpose of the primary key is to hold the table’s data. Changing the primary key so it starts with a very-commonly-used column (for example, post_id in some tables) makes lookups faster: the database server has immediate access to all the data in the found rows. The other columns in the primary key help with more complex lookups, and ensure the primary key is always a unique identifer.
Data Definition Language
If you want to know exactly what the plugin does, you can find the DDL (the ALTER TABLE statements) for the Barracuda storage engine here. The DDL for the Antelope storage engine is here.
Tables and their keys
Here is a table-by-table rundown of the WordPress tables we affect and their keys.
wp_comments
This table holds your site’s comments and product reviews: all the content contributed by users. WooCommerce also uses it to keep track of order progress.
Key | WordPress standard key columns | High performance key columns (A and B) |
---|---|---|
PRIMARY KEY | comment_ID | comment_post_ID, comment_ID |
comment_ID (unique) | (not present) | comment_ID |
comment_approved_date_gmt | comment_approved, comment_date_gmt | (unchanged) |
comment_parent | comment_parent | comment_parent, comment_ID |
comment_author_email | comment_author_email(10) | comment_author_email, comment_post_ID, comment_ID |
comment_post_parent_approved | (not present) | comment_post_ID, comment_parent, comment_approved, comment_type, user_id, comment_date_gmt, comment_ID |
Changing the primary key so it starts with post id makes lookups by post id faster: the database server has immediate access to all the rows’s data upon finding a comment for a particular post. The comment_ID column is also part of the compound primary key because the primary key still must be unique. A post can have many comments, obviously, so the post id alone is not unique.
The new unique comment_ID
index exists because MySQL needs it. MySQL generates its comment_ID
values by autoincrementing them, and the database software requires there to be a key on the autoincrementing column.
We change comment_author_email
from a very simple key on the first ten characters of the author’s email to a compound key. The first part of the compound key is the author’s email, and the second is the post id. This high-performance key speeds up the operation of searching for all the posts where a particular author has made a comment.
The new key comment_post_parent_approved
speeds up a common lookup by those columns in the WordPress software.
wp_commentmeta
Comment metadata is extra data associated with each comment. The Akismet anti-spam system uses it to keep track of its screening of each comment. And, various other plugins use it too.
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | meta_id | meta_key, comment_id, meta_id | comment_id, meta_id |
meta_id (unique) | (not present) | meta_id | meta_id |
comment_id | comment_id | comment_id, meta_key, meta_value(32) | comment_id, meta_key(32) |
meta_key | meta_key(191) | (not present) | meta_key(32), meta_value(32) |
meta_value | (not present) | meta_value(32) | meta_value(32) |
A very common lookup pattern in WordPress is by meta_key
and comment_id
. For example, Akismet looks up its own meta_key
for each comment it processes. Barracuda lets us put the entire meta_key
column in the primary key, so we get quick data retrieval for that column. Antelope can’t index the whole 250-character meta_key
column, so we can’t use it in the primary key.
See the discussion about the primary key for wp_comments
for an explanation of why we use a compound primary key. And, see the discussion of the comment_ID
key for why we use the meta_id
key.
Another common lookup searches for data with particular comment_id
, meta_key
and meta_value
columns. The plugin converts WordPress’s column_id
key to a compound key for this case. For Antelope databases it converts the meta_key
column to a compound key.
We add the meta_value
key to cover lookups by meta_value
. The meta_value
column can contain very long values, so we must use a prefix key for it, hence meta_value(32)
.
wp_posts
wp_posts
holds your content: posts, pages, products, orders, media, and other items. WordPress, and themes, and plugins, look up those items in many different ways. So, the plugin adds columns to its indexes to cover those lookup patterns.
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | ID | (unchanged) | (unchanged) |
post_name | post_name(191) | post_name | post_name(32) |
post_parent | post_parent | post_parent, post_type, post_status | (same as B |
type_status_date | post_type, post_status, post_date, ID | post_type, post_status, post_date, post_author | (same as B) |
post_author | post_author | post_author, post_type, post_status, post_date | (same as B) |
wp_postmeta
wp_postmeta
holds metadata about the items in your wp_posts
table. The table is very heavily used. Metadata can be anything: SEO information, pricing, and so forth.
The wp_postmeta
, wp_usermeta
, and wp_termmeta
tables share similar structures. Our high-performance keys for those three tables are also similar.
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | meta_id | post_id, meta_key, meta_id | post_id, meta_id |
meta_id (unique) | (not present) | meta_id | meta_id |
post_id | post_id | (not present) | post_id, meta_key(32), meta_value(32), meta_id |
meta_key | meta_key(191) | meta_key, meta_value(32), post_id, meta_id | meta_key(32), meta_value(32), meta_id |
meta_value | (not present) | meta_value(32), meta_id | meta_value(32), meta_id |
A lot of software looks up post metadata by post_id
, so that’s the first column in the compound primary key. Some software looks up post metadata by post_id
and meta_key
, so if we can (in Barracuda) we include that column in the primary key. For more information, see the discussion about primary keys for the wp_commentmeta
table earlier in this article.
Some software looks up records by meta_key
and meta_value
. For example, WooCommerce keeps its product prices and other per-product information in this table. So we’ve added prefix indexes on meta_value.
wp_users
We have added one index to the wp_users
table to accomodate software which looks up users by their display names. All the other indexes are unchanged.
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | ID | (unchanged) | (unchanged) |
user_login_key | user_login_key | (unchanged) | (unchanged) |
user_nicename | user_nicename | (unchanged) | (unchanged) |
user_email | user_email | (unchanged) | (unchanged) |
display_name | (not present) | display_name | display_name(32) |
wp_usermeta
wp_usermeta
works like wp_postmeta
, except that it holds metadata about your users. Various software uses it in various different ways. In sites with many users, this table requires high-performance keys to avoid slowdowns.
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | umeta_id | user_id, meta_key, umeta_id | post_id, umeta_id |
umeta_id (unique) | (not present) | umeta_id | umeta_id |
user_id | user_id | (not present) | user_id, meta_key(32), meta_value(32), umeta_id |
meta_key | meta_key(191) | meta_key, meta_value(32), user_id, umeta_id | meta_key(32), meta_value(32), umeta_id |
meta_value | (not present) | meta_value(32), umeta_id | meta_value(32), umeta_id |
wp_termmeta
WordPress’s taxonomy subsystem handles such things as post tags and categories. wp_termmeta
does for the taxonomy system what wp_postmeta
does for posts and wp_usermeta
does for users.
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | meta_id | term_id, meta_key, meta_id | term_id, meta_id |
meta_id (unique) | (not present) | meta_id | meta_id |
term_id | term_id | (not present) | term_id, meta_key(32), meta_value(32), meta_id |
meta_key | meta_key(191) | meta_key, meta_value(32), term_id, meta_id | meta_key(32), meta_value(32), meta_id |
meta_value | (not present) | meta_value(32) | meta_value(32), meta_id |
wp_options
wp_options
holds your site-wide information, including such things as its name, its time zone, and the settings of various plugins and themes.
Key | WordPress standard key columns | High-performance key columns (A and B) |
---|---|---|
PRIMARY KEY | option_id | option_name |
option_id (unique) | (not present) | option_id |
option_name (unique) | option_name | (not present) |
autoload | autoload | (unchanged) |
WordPress most often accesses this table in one of two ways.
- Looking up, changing, or deleting a single option item by its
option_name
. - At the beginning of each page view, automatically preloading all the options marked for
autoload
.
Each page view on a large site uses several, sometimes even dozens, of the first kind of access. It uses at most one of the second kind of access. And, many large sites use a WordPress Object Cache, which entirely avoids most of the second kind of access. If you operate a large site, please consider using a Persistent Cache plugin as well as this plugin.
The plugin’s keys on the wp_options
table are designed mostly to speed up the first kind of operation. We use the option_name
column for the primary key, and add a unique key for option_id
.
Early versions of WordPress’s definition for wp_options
did not have the autoload
key, but modern versions do.
wp_woocommerce_order_itemmeta
This WooCommerce table has a similar structure to wp_postmeta, and this plugin adds keys in a similar way. It is used to hold descriptions of items included in orders from store customers.
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | meta_id | order_item_id, meta_key, meta_id | order_item_id, meta_id |
meta_id (unique) | (not present) | meta_id | meta_id |
order_item_id | term_id | (not present) | order_item_id, meta_key(32), meta_value(32), meta_id |
meta_key | meta_key(191) | meta_key, meta_value(32), order_item_id, meta_id | meta_key(32), meta_value(32), order_item_id, meta_id |
meta_value | (not present) | meta_value(32), meta_id | meta_value(32), meta_id |
wp_wc_orders_meta
This WooCommerce table has a similar structure to wp_postmeta, and this plugin adds keys in a similar way. It is used to hold data about orders including such things as payment gateway transaction IDs and customer addresses.
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | id | order_id, meta_key, id | order_id, id |
id (unique) | (not present) | id | id |
order_id_meta_key_meta_value | order_id, meta_key(191), meta_value(100) | (not present) | (not present) |
meta_key_value | meta_key(191) | meta_key, meta_value(32), order_id, id | meta_key(32), meta_value(32) |
meta_value | (not present) | meta_value(32), meta_id | meta_value(32), meta_id |
automatewoo_log_meta
This table comes from the AutomateWoo extension to WooCommerce. It too has a similar structure to wp_postmeta, and this plugin adds keys in a similar way. This table is used to hold miscellaneous extra data about automation jobs within WooCommerce,
Key | WordPress standard key columns | B high-performance key columns | A high-performance key columns |
---|---|---|---|
Primary Key | meta_id | log_id, meta_key, meta_id | order_item_id, meta_id |
meta_id (unique) | (not present) | meta_id | meta_id |
log_id | log_id | (not present) | log_id, meta_key(32), meta_value(32), meta_id |
meta_key | meta_key(191) | meta_key, meta_value(32), log_id, meta_id | meta_key(32), meta_value(32), order_item_id, meta_id |
meta_value | (not present) | meta_value(32), meta_id | meta_value(32), meta_id |
Hi Ollie,
Will this workk for custom post types that is not woocommerce ?
Best regards,
Hervé
Yes, it will.
Can we remove the app after we’ve converted the tables? Thanks.
Yes, you can deactivate and uninstall the plugin after adding the high-performance keys.
Hi Ollie!
I’ve been using this wonderful plugin for some years now and I am wondering id there is any way to unset the variable that displays the database update message. When I update from wp-cli it doesn’t get unset and I have to go to the plugin admin page to get rid of it.
Thanks!
Brian Brown
Good catch, Dr. Brown! Thanks.
https://github.com/OllieJones/index-wp-mysql-for-speed/issues/70
So, we have a query for an internal linking plugin that is going very slow because it is querying the post content for 30k+ posts.
Would this speed it up? Reading above the keys updated in the wp_posts doesn’t seem to be the actual post content.
It’s impossible to say whether the plugin will speed up a particular set of queries without seeing the details. You can use the Query Monitor plugin, or this plugin’s Monitor feature, to see what the queries in question specify. We definitely don’t try to index LONGTEXT columns like `post_content`.
ALTER TABLE wp_posts ADD KEY wp_posts_idx_post_status_post_type (post_status, post_type);
This one will cut about 1/3 of the time off of _pad_term_counts() in Woocommerce.
SELECT object_id, term_taxonomy_id
FROM wp_term_relationships
INNER JOIN wp_posts
ON object_id = ID
WHERE term_taxonomy_id IN (3156,17,18,19,21,26,22,3464,3368,67,70,66,71,74,75,65,72,68,73,69,25,27)
AND post_type IN ('product')
AND post_status = 'publish'
Standard WordPress offers a key on
wp_posts(post_type, post_status, post_date, ID)
. In all the versions of MariaDB and MySQL I’ve tested, that key also accelerates the WooCommerce query you mentioned. Still, I will check this out. Thanks.