Add MySQL Index to WordPress wp_options Table

The wp_options table can be very bloated due to all the settings placed there by themes and plugins. I clean up WordPress options tables quite often for my clients on Codeable to help speed up the wp-admin dashboard area. If you use a plugin that stores a lot of transient or session data in the options table then there are two ways to speed this up: using object cache and adding a MySQL index. The object cache solution will put your options table in a daemon like Redis or Memcached so the values can be fetched from RAM. The other solution is adding an index to the wp_options table so that finding the relevant options takes less time (a MySQL index is similar to an index in a large book).

In this post I will show you how to find out if an index is already added, how to find out if an index is a good idea and finally how to add and remove the wp_options index to see if it improves performance. I highly recommend using New Relic to measure database query time if possible, both Kinsta and Cloudways offer New Relic as part of their hosting services.

Add MySQL Index to WordPress wp_options Table

First find out if any index exists, change wp_options in case you changed your database prefix in wp-config.php or with a plugin.

You can run these commands from phpMyAdmin, Adminer or using WP-CLI‘s wp db query command.

You can also use the SQL Executioner plugin for executing these.

SHOW INDEX FROM wp_options;

Here is the WP-CLI version

wp db query "SHOW INDEX FROM $(wp db prefix --allow-root)options;" --allow-root

If you only see the table below then there is no index present

+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|   Table    | Non_unique |  Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_options |          0 | PRIMARY     |            1 | option_id   | A         |         511 | NULL     | NULL   |      | BTREE      |         |               |
| wp_options |          0 | option_name |            1 | option_name | A         |         511 | NULL     | NULL   |      | BTREE      |         |               |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

But if you see an index with a Column_name value of autoload or something similar then you already have an index.

+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|   Table    | Non_unique |  Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_options |          0 | PRIMARY     |            1 | option_id   | A         |         670 | NULL     | NULL   |      | BTREE      |         |               |
| wp_options |          0 | option_name |            1 | option_name | A         |         670 | NULL     | NULL   | YES  | BTREE      |         |               |
| wp_options |          1 | autoload    |            1 | autoload    | A         |           1 | NULL     | NULL   |      | BTREE      |         |               |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

A shortcut for listing any wp_options MySQL indices is this command

SHOW INDEX FROM wp_options WHERE column_name != 'option_id' AND column_name != 'option_name';

Here is the WP-CLI version

wp db query "SHOW INDEX FROM $(wp db prefix --allow-root)options WHERE column_name != 'option_id' AND column_name != 'option_name';" --allow-root

First find out if it is a good idea to add an index to your wp_options table by comparing the number of autoload = yes rows to the number of autoload = no rows

SELECT COUNT(CASE WHEN autoload = 'yes' THEN 1 END) FROM wp_options;

WP-CLI version

wp db query "SELECT COUNT(CASE WHEN autoload = 'yes' THEN 1 END) AS AutoloadYes FROM $(wp db prefix --allow-root)options;" --allow-root

Find out how many are not autoloaded with this query

SELECT COUNT(CASE WHEN autoload = 'no' THEN 1 END) FROM wp_options;

WP-CLI version

wp db query "SELECT COUNT(CASE WHEN autoload = 'no' THEN 1 END) AS AutoloadNo FROM $(wp db prefix --allow-root)options;" --allow-root

Generally you only want to make an index if the number of autoload = no options greatly outweigh the autoload = yes options.

As a rule of thumb if 60-80% of the option_name keys are autoload = no values then an index is a good idea.

The MySQL CREATE INDEX syntax takes this format (source)

CREATE INDEX <index-name> ON <table-name>(<column-1>, <column-2>);

You can create the WordPress MySQL index on the wp_options table like this

CREATE INDEX autoloadindex ON wp_options(autoload, option_name);

You can also do it with WP-CLI

wp db query "CREATE INDEX autoloadindex ON $(wp db prefix --allow-root --skip-plugins --skip-themes)options(autoload, option_name);" --allow-root

If you use New Relic (see the benefits with this Kinsta tutorial) you can see the improvement in transaction time, notice how the brown section almost disappears around 8 AM in the graph!

If you want to delete the index, this statement will drop the MySQL index from your wp_options table

DROP INDEX autoloadindex ON wp_options

Here is the WP-CLI version

wp db query "DROP INDEX autoloadindex ON $(wp db prefix --allow-root)options" --allow-root

If you would like to learn more about MySQL indexes in general here is a very thorough post on the topic.

Sources

Stackexchange wp options Table Index
Stackexchange wp_options autoload Index
Cardinality in MySQL Index
Drop Index on MySQL
Optimizing WooCommerce for Thousands of Products
Show Index for MySQL Tables
Difference between ALTER TABLE and CREATE INDEX