Speeding up WordPress database operations

Rick James and I have cooked up a plugin, Index WP MySQL for Speed, to help optimize the way WordPress uses its MySQL database. It adds indexes to WordPress’s tables in MySQL to allow them to deliver data more efficiently without needing WordPress code changes. Users do not see any change in functionality, only in performance.

The basic idea

• It’s an ordinary WordPress plugin, downloadable from the plugin repository.
• As of June 2021 it’s released on the repository. It’s a work in progress. It’s here on github.
• Once the plugin is activated, the the site administrator uses it by choosing Index MySQL from the Tools menu on the WordPress dashboard. Or the administrator can use WP-CLI to run it from a command-line interface on the server.
• The plugin examines …
  • the WordPress and php versions,
  • the MySQL / MariaDB version, and
  • the sizes of various tables.
• If the versions are too old (or too new), the plugin announces that it can’t help.
• If the tables use MySQL’s old MyISAM storage engine, the plugin’s Settings screen offers the option to upgrade them to the more modern InnoDB engine.
• It then offers to add high-performance keys (also known as indexes in database jargon) to certain tables.
• The WordPress site admin may choose tables to receive high-performance keys, and the plugin adds them.

Once the high-performance keys are in place, the plugin’s settings page offers to revert them to the WordPress standard keys. That’s so you can undo the plugin’s work if you need to. Most site owners will not revert the keys to WordPress standard.

Once its work is done, the plugin gets out of the way. If the plugin is deactivated or deleted, the high-performance keys remain in place.

With the administrator’s permission, the plugin posts anonymous data about the MySQL instance to the plugin’s servers. This helps Rick and me understand user configurations, with a view towards improving the plugin.

Security

Nobody except site administrators will see anything about this plugin in a site.

Related plugins

There are a mess of database cleaner plugins available. These work by DELETEing no-longer-used rows from various tables and performing OPTIMIZE TABLE operations. A good example is Advanced Database Cleaner.

Query Monitor is a mature and sophisticated WordPress developer tool. It intercepts (“hooks“, in WordPress lingo) database queries and captures information about them.

Specifics

The first version of the plugin changes the indexing (keying) of these tables:

• wp_postmeta: metadata describing posts, pages, media, and WooCommerce products
• wp_usermeta: metadata describing registered users and WooCommerce customers
• wp_termmeta: metadata describing attributes and categories (known as taxonomies)
• wp_options: configuration data for WordPress, its themes, and its plugins
• wp_posts: the content of posts, pages, and WooCommerce products
• wp_comments: comments on posts.

In most WordPress installations these six tables contain the majority of the content.

In a WordPress installation’s MySQL or MariaDB open-source database management sysem the first four of these tables have a name-value design, making it possible to add all sorts of custom fields and other items to posts, users, and taxonomies. Their flexibility has allowed WordPress to develop a vast ecosystem of themes and plugins.

For example a used-car dealers’ plugin might create an entry in wp_posts describing each car for sale. wp_postmeta might contain entries like these.

But flexibility comes at a cost: querying these name-value tables can be slow. In this example, to find all the cars from 2014 requires filtering the wp_postmeta table to examine only the rows with the meta_key value equal to ‘year’. WordPress would do it with a query like this.

SELECT wp_posts.ID, wp_posts.title,
       make.meta_value make,
       model.meta_value model,
       year.meta_value year
  FROM wp_posts
  LEFT JOIN wp_postmeta make ON wp_posts.ID = make.post_id
                            AND make.meta_key = 'make'
  LEFT JOIN wp_postmeta model ON wp_posts.ID = model.post_id
                             AND model.meta_key = 'model'
  LEFT JOIN wp_postmeta year ON wp_posts.ID = year.post_id
                            AND year.meta_key = 'year'
 WHERE year.meta_value = 2014;

This plugin adds keys to the wp_postmeta table to help speed up this sort of database query. (The words index and key are synonyms in the world of database management: WordPress’s designers prefer the word key.)

Database management systems are designed to have their keys updated, adjusted, and tweaked as they grow. In a new WordPress instance with a couple of users and a dozen posts, the keys don’t matter. But as a site (especially a WooCommerce site) grows and becomes successful, the keys start to matter a lot. The keys are there to help find the data quickly. For example, if you have ten thousand customers and you want to look somebody up by their billing postcode, it helps to have a key to do that. Without the key the database still finds the customer, but it scans through all the customers. We all know what that looks like: it’s slow.

Adding, dropping, or altering keys doesn’t change the underlying data. It is a routine maintenance task in many data centers. If changing keys caused systems to lose data, the MySQL and MariaDB developers would hear howling not just from you and me, but also from many other heavyweight users. (You should still back up your WordPress instance.)

The wp_posts and wp_comments tables also get some keys to speed up common query patterns.

How can changing keys make a difference? (wonky)

Modern MySQL databases store their data in tables using a storage engine called InnoDB. (Early versions of MySQL used a different, simpler, storage engine, called MyISAM. If you are still using that, it is time to upgrade. Seriously.) WordPress’s database tables all have a primary key. Think of the primary key as a book’s catalog number in a library. You look up, for example Sheeri Cabral and Keith Murphy’s excellent MySQL Administrator’s Bible, in your library’s online catalog by searching for “MySQL” or “Database Administration.” Your online lookup gives you the book’s catalog number. You then wander around your library looking for the shelf containing books with numbers like that. When you find it, you take out the book. InnoDB primary keys work like that (but without all the wandering around). Once it knows the primary key, it can grab the data very quickly. In the world of database management, this is called clustered indexing. It follows that a good choice of primary key can make it very fast for InnoDB to find data.

InnoDB also offers secondary keys. A secondary key holds search terms like the “MySQL” or “Database Administration” we used to find Sheeri’s book. Those secondary keys lead us to the primary key. We can think of InnoDB’s keys as if they were sorted in alphabetical order. (Technically speaking they use the B-tree data structure.) For example, the author key might contain “Cabral, Sheeri” and “Murphy, Keith.” If I looked up “Cabral” in the author key I’d find Sheeri right away, get the primary key, and grab her book. (This takes fractions of milliseconds in InnoDB.) But, if I looked up “Sheeri” I would have to scan every author’s name to find her: There might be authors named “Aardvark, Sheeri” and “Zyzygy, Sheeri”. I know there aren’t, but the software doesn’t. That takes time. WordPress gets slow when it uses its keys that way. To make this lookup faster we add a new secondary key on authors’ first names.

So, we can adjust the primary and secondary keys to make it faster for WordPress to get what it needs from its database tables. This plugin does that.

MySQL (along with other database management systems) offer composite keys: keys made from multiple columns. Composite keys speed up searching on multiple criteria at once. For example, a search in wp_postmeta for a particular post_id and meta_key benefits from a composite key on both columns.

What specific key changes do we make? (even wonkier)

Primary keys serve two purposes. They uniquely identify their data, and they handle the rapid-lookup clustered indexing. Their unique identification purpose means that database designers often set up tables to give each item — each row of data — an automatically incrementing serial number for a primary key. Once you know the serial number you can rapidly grab the item from the clustered index. But if you come at the data with some other way of identifying the item, you get an extra lookup step and that slows you down.

For example, the wp_options table contains dozens of rows that WordPress retrieves every time somebody views a page. The row with the option_name of “home”, for example, contains https://plumislandmedia.net for this WordPress instance. To get this information, WordPress says this to its database.

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'

The wp_options table has one of those automatically incrementing primary keys, where each row has a number. It’s called option_id. And, it has a key on the “autoload” column of data to help speed up filtering by autoload = ‘yes’. That’s a competently designed table (of course! WordPress’s developers are poets). But we can do better, especially considering how often we must get all the autoload rows. We can change the table’s primary key so it includes two columns rather than one: autoload and option_id. It still serves the uniqueness purpose: the option_ids are unique. But putting autoload first in the primary key means MySQL can retrieve the autoloaded rows directly from the clustered index, rather than looking in a secondary key to find the primary key. The saved milliseconds and microwatts add up, especially on a busy site. So we change the primary key like this.

ALTER TABLE wp_options ADD PRIMARY KEY (autoload, option_id)

The actual changes are a little more involved than that, but you get the idea.

These are the standard keys and the high-performance keys for the six tables we handle. In the first four cases we change the tables’ primary keys to composite keys matching the most common search patterns in the WordPress software.

Data Definition Language

The plugin runs these data definition language statements to add the high performance keys to databases running on MySQL or MariaDB version 5.6 or better. Of course, site administrators can run these statements themselves via phpmyadmin or some other MySQL client instead of using the plugin.

ALTER TABLE wp_postmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_postmeta DROP PRIMARY KEY;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta DROP KEY post_id;
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);

ALTER TABLE wp_usermeta ADD UNIQUE KEY umeta_id (umeta_id);
ALTER TABLE wp_usermeta DROP PRIMARY KEY;
ALTER TABLE wp_usermeta ADD PRIMARY KEY (user_id, meta_key, umeta_id);
ALTER TABLE wp_usermeta DROP KEY user_id;
ALTER TABLE wp_usermeta DROP KEY meta_key;
ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key, user_id);

ALTER TABLE wp_termmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_termmeta DROP PRIMARY KEY;
ALTER TABLE wp_termmeta ADD PRIMARY KEY (term_id, meta_key, meta_id);
ALTER TABLE wp_termmeta DROP KEY term_id;
ALTER TABLE wp_termmeta DROP KEY meta_key;
ALTER TABLE wp_termmeta ADD KEY meta_key (meta_key, term_id);

ALTER TABLE wp_options ADD UNIQUE KEY option_id (option_id);
ALTER TABLE wp_options DROP PRIMARY KEY;
ALTER TABLE wp_options ADD PRIMARY KEY (autoload, option_id);
ALTER TABLE wp_options DROP KEY autoload;

ALTER TABLE wp_posts DROP KEY type_status_date;
ALTER TABLE wp_posts ADD KEY type_status_date
                     (post_type, post_status, post_date, post_author, ID);
ALTER TABLE wp_posts DROP KEY post_author;
ALTER TABLE wp_posts ADD KEY post_author
                     (post_author, post_type, post_status, post_date, ID);

ALTER TABLE wp_comments ADD KEY comment_post_parent_approved
                     (comment_post_ID, comment_parent, comment_approved, comment_ID);

Reverting to the WordPress standard keys uses this data definition language.

ALTER TABLE wp_postmeta DROP PRIMARY KEY;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE wp_postmeta DROP KEY meta_id;
ALTER TABLE wp_postmeta ADD KEY post_id (post_id);
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191));

ALTER TABLE wp_usermeta DROP PRIMARY KEY;
ALTER TABLE wp_usermeta ADD PRIMARY KEY (umeta_id);
ALTER TABLE wp_usermeta DROP KEY umeta_id;
ALTER TABLE wp_usermeta ADD KEY user_id (user_id);
ALTER TABLE wp_usermeta DROP KEY meta_key;
ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key(191));

ALTER TABLE wp_termmeta DROP PRIMARY KEY;
ALTER TABLE wp_termmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE wp_termmeta DROP KEY meta_id;
ALTER TABLE wp_termmeta DROP KEY meta_key;
ALTER TABLE wp_termmeta ADD KEY meta_key (meta_key(191));
ALTER TABLE wp_termmeta ADD KEY term_id (term_id);

ALTER TABLE wp_options DROP PRIMARY KEY;
ALTER TABLE wp_options ADD PRIMARY KEY (option_id);
ALTER TABLE wp_options DROP KEY option_id;
ALTER TABLE wp_options ADD KEY autoload (autoload);

ALTER TABLE wp_posts DROP KEY type_status_date;
ALTER TABLE wp_posts ADD KEY type_status_date (post_type, post_status, post_date, ID);
ALTER TABLE wp_posts DROP KEY post_author;
ALTER TABLE wp_posts ADD KEY post_author (post_author);

ALTER TABLE wp_comments DROP KEY comment_post_parent_approved;

Data Definition Language for Older MySQL Versions

When the MySQL version is 5.5 or older, there’s a complication: Keys can only cover the first 191 characters of text columns. This is because they’re limited in length to 767 bytes, and we must allocate four bytes to every Unicode character. Those limited-length keys require different data definition language statements. Given that constraint, the plugin uses these data definition language statements to add the highest-possible performance keys.

ALTER TABLE wp_postmeta ADD UNIQUE KEY meta_id (meta_id)
ALTER TABLE wp_postmeta DROP PRIMARY KEY
ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_id)
ALTER TABLE wp_postmeta DROP KEY post_id
ALTER TABLE wp_postmeta ADD KEY post_id (post_id, meta_key(191))
ALTER TABLE wp_postmeta DROP KEY meta_key
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191), post_id)
ALTER TABLE wp_usermeta ADD UNIQUE KEY umeta_id (umeta_id)

ALTER TABLE wp_usermeta DROP PRIMARY KEY
ALTER TABLE wp_usermeta ADD PRIMARY KEY (user_id, umeta_id)
ALTER TABLE wp_usermeta DROP KEY user_id
ALTER TABLE wp_usermeta ADD KEY user_id (user_id, meta_key(191))
ALTER TABLE wp_usermeta DROP KEY meta_key
ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key(191), user_id)

ALTER TABLE wp_termmeta ADD UNIQUE KEY meta_id (meta_id)
ALTER TABLE wp_termmeta DROP PRIMARY KEY
ALTER TABLE wp_termmeta ADD PRIMARY KEY (term_id, meta_id)
ALTER TABLE wp_termmeta DROP KEY term_id
ALTER TABLE wp_termmeta ADD KEY term_id (term_id, meta_key(191))
ALTER TABLE wp_termmeta DROP KEY meta_key
ALTER TABLE wp_termmeta ADD KEY meta_key (meta_key(191), term_id)

ALTER TABLE wp_options ADD UNIQUE KEY option_id (option_id)
ALTER TABLE wp_options DROP PRIMARY KEY
ALTER TABLE wp_options ADD PRIMARY KEY (autoload, option_id)
ALTER TABLE wp_options DROP KEY autoload

ALTER TABLE wp_posts DROP KEY type_status_date
ALTER TABLE wp_posts ADD KEY type_status_date 
                     (post_type, post_status, post_date, post_author, ID)
ALTER TABLE wp_posts DROP KEY post_author
ALTER TABLE wp_posts ADD KEY post_author (post_author, post_type, post_status, post_date, ID)

ALTER TABLE wp_comments ADD KEY comment_post_parent_approved 
                     (comment_post_ID, comment_parent, comment_approved, comment_ID)

On these older versions of MySQL, reverting to the WordPress standard keys uses this data definition language.

ALTER TABLE wp_postmeta DROP PRIMARY KEY;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE wp_postmeta DROP KEY meta_id;
ALTER TABLE wp_postmeta DROP KEY post_id;
ALTER TABLE wp_postmeta ADD KEY post_id (post_id);
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191));

ALTER TABLE wp_usermeta DROP PRIMARY KEY;
ALTER TABLE wp_usermeta ADD PRIMARY KEY (umeta_id);
ALTER TABLE wp_usermeta DROP KEY umeta_id;
ALTER TABLE wp_usermeta DROP KEY user_id;
ALTER TABLE wp_usermeta ADD KEY user_id (user_id);
ALTER TABLE wp_usermeta DROP KEY meta_key;
ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key(191));

ALTER TABLE wp_termmeta DROP PRIMARY KEY;
ALTER TABLE wp_termmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE wp_termmeta DROP KEY meta_id;
ALTER TABLE wp_termmeta DROP KEY meta_key;
ALTER TABLE wp_termmeta ADD KEY meta_key (meta_key(191));
ALTER TABLE wp_termmeta DROP KEY term_id;
ALTER TABLE wp_termmeta ADD KEY term_id (term_id);

ALTER TABLE wp_options DROP PRIMARY KEY;
ALTER TABLE wp_options ADD PRIMARY KEY (option_id);
ALTER TABLE wp_options DROP KEY option_id;
ALTER TABLE wp_options ADD KEY autoload (autoload);

ALTER TABLE wp_posts DROP KEY type_status_date;
ALTER TABLE wp_posts ADD KEY type_status_date (post_type, post_status, post_date, ID);
ALTER TABLE wp_posts DROP KEY post_author;
ALTER TABLE wp_posts ADD KEY post_author (post_author);

ALTER TABLE wp_comments DROP KEY comment_post_parent_approved;