Bobcares

Add MySQL Index To WordPress WP_Options Table | How To?

PDF Header PDF Footer

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.

add mysql index to wordpress wp_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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

2 Comments
  1. jalakslot bandar togel

    Link exchange is nothing else however it is only placing the other
    person’s webpage link on your page at suitable place
    and other person will also do same for you.

    Reply
    • Liya Augustine

      Hello,
      If you have any issue, Please contact our support team via live chat(click on the icon at right-bottom). Or if our article helps you We are glad to know that our article was helpful for you

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Get featured on the Bobcares blog and share your expertise with a global tech audience.

WRITE FOR US
server management

Custom WordPress Development Solutions for Your Business

Talk to us

Or click here to learn more.

Speed issues driving customers away?
We’ve got your back!