Add MySQL Index To WordPress WP_Options Table | How To?

Let’s see the steps to add MySQL index to the WordPress wp_options table. With our WordPress Support Services, Bobcares provides solutions to your WordPress-related queries.

How to add MySQL index to WordPress wp_options table?

Adding an index to the wp_options table is one way to speed up the options table if we use a plugin that stores many temporary or session data there. This is because choosing the suitable options requires less time (a MySQL index is similar to an index in a large book). This post will show the steps to add MySQL index to the WordPress options table.

Steps to add an index to the WordPress options table

1. Firstly, we need to check to see if any indexes already exist, then modify wp_options if we modify the database prefix either in wp-config.php or with a plugin. On WP-CLI, run the below code:

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

2. If the resulting table has an index with a Column_name value of autoload or something similar then we already have an index. E.g.,

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

3. We can use this command on WP-CLI to quickly list all wp_options MySQL indices:

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

4. Then by comparing the number of autoload = yes rows to the number of autoload = no rows, we can decide whether it is wise to add an index to the wp_options table.

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

5. On WP-CLI, use this code to locate how many are not autoloaded:

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

6. In general, we should only create an index if the number of autoload = no options greatly outweighs the autoload = yes options. An index is a good idea if 60–80% of the option_name keys are autoload = no values.

7. The MySQL CREATE INDEX syntax takes this format:

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

8. We can create the WP MySQL index on the wp_options table like this using WP-CLI:

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

With the addition of the MySQL index to the WordPress wp_options table, we can see an improvement in transaction time.

[Looking for a solution to another query? We’re here to help.]

Conclusion

In this article, we provide a quick method to add the MySQL index to the WordPress wp_options table.