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 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;

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';

First find out if it is a good idea to add an index to your wp_optins 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;

Find out how many are not autoloaded

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

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

CREATE INDEX autoload ON wp_options(autoload, option_name);

If you use New Relic 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 for your wp_options table

DROP INDEX autoload ON wp_options

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