Tables and Keys

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.

Image: MySQL upgrade 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 2021 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 KEYcomment_IDcomment_post_ID, comment_ID
comment_ID (unique)(not present)comment_ID
comment_approved_date_gmtcomment_approved, comment_date_gmt(unchanged)
comment_parentcomment_parentcomment_parent, comment_ID
comment_author_emailcomment_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
wp_comments keys

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

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.

KeyWordPress standard key columnsB high-performance key columnsA high-performance key columns
Primary Keymeta_idmeta_key, comment_id, meta_idcomment_id, meta_id
meta_id (unique)(not present)meta_idmeta_id
comment_idcomment_idcomment_id, meta_key, meta_value(32)comment_id, meta_key(32)
meta_keymeta_key(191)(not present)meta_key(32), meta_value(32)
meta_value(not present)meta_value(32)meta_value(32)
wp_commentmeta keys

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 KeyID(unchanged)(unchanged)
post_namepost_name(191)post_namepost_name(32)
post_parentpost_parentpost_parent, post_type, post_status(same as B
type_status_datepost_type, post_status, post_date, IDpost_type, post_status, post_date, post_author(same as B)
post_authorpost_authorpost_author, post_type, post_status, post_date(same as B)
wp_posts keys

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 columnsA high-performance key columns
Primary Keymeta_idpost_id, meta_key, meta_idpost_id, meta_id
meta_id (unique)(not present)meta_idmeta_id
post_idpost_id(not present)post_id, meta_key(32), meta_value(32), meta_id
meta_keymeta_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_idmeta_value(32), meta_id
wp_postmeta keys

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 columnsB high-performance key columnsA high-performance key columns
Primary KeyID(unchanged)(unchanged)
user_login_keyuser_login_key(unchanged) (unchanged)
user_nicenameuser_nicename(unchanged) (unchanged)
user_emailuser_email(unchanged) (unchanged)
display_name(not present)display_namedisplay_name(32)
wp_users keys

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 columnsB high-performance key columnsA high-performance key columns
Primary Keyumeta_iduser_id, meta_key, umeta_idpost_id, umeta_id
umeta_id (unique)(not present)umeta_idumeta_id
user_iduser_id(not present)user_id, meta_key(32), meta_value(32), umeta_id
meta_keymeta_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_idmeta_value(32), umeta_id
wp_usermeta keys

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 columnsB high-performance key columnsA high-performance key columns
Primary Keymeta_idterm_id, meta_key, meta_idterm_id, meta_id
meta_id (unique)(not present)meta_idmeta_id
term_idterm_id(not present)term_id, meta_key(32), meta_value(32), meta_id
meta_keymeta_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_termmeta keys

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.

KeyWordPress standard key columns High-performance key columns
(A and B)
PRIMARY KEYoption_idoption_name
option_id (unique)(not present)option_id
option_name (unique)option_name(not present)
autoloadautoload(unchanged)
wp_options keys

WordPress most often accesses this table in one of two ways.

  1. Looking up, changing, or deleting a single option item by its option_name.
  2. 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.


2 thoughts on “Tables and Keys”

  1. 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'

    Reply

Leave a Comment