How to add an index in the WordPress options table and why you should do it • WordPress Help

Table options ( wp_options by default, or ideally with your custom prefix) of WordPress is the one that goes accumulating information about each of the plugins, settings and configurations and more, that you are installing throughout the life of your website.

If we add that the Most plugins do not delete lines from this table, you'll see that is gradually becoming a monster with thousands of lines, which WordPress checks and launches on each page load .

As you can imagine, a table options large can greatly slow down your website since your WordPress must make many requests and launch requests before loading each page for each visitor.

Ergo, if we could limit this amount ad requests your web would load faster, having to make fewer queries, right?

The culprit: Autoload

The culprit of all this is the field Autoload of each line of the table options, usually with the value in Yes that is, yes, it is automatically loaded by the function wp_load_alloptions () .

What's the problem with Autoload?

Well, very simple, it automatically loads all options from table options though:

  • The plugin option does not need to load on each page (Example: contact form that loads its settings across the web when it is only on one page.)
  • The plugin is no longer installed and active.
  • The theme is no longer active.
  • The plugin should have created their own tables and do not put their options in the table options

And, the green The problem is when it self-loads too much but what is too much?

Important note : In this article, we will work directly with the database. Before executing any query or touching something in it, make a backup copy.

Execute this SQL command, you can do it in PHPMyAdmin of your hosting panel:

Note: Change the example prefix to yours, do not forget

Click on the button Continue or execute the SQL query and you will get the size of the query of all the values ​​with autoload e bytes. And the rule should be:

  • If the result of autoload_size is greater than 1 Mb (approximately 1,000,000 bytes) you should do something to optimize this.

the example of the capture size is ridiculous, so if your results are like that you have just read the article, dedicate your time to blog something.

If you obtained a result above 1Mb, or just want to dedicate a Having said that,

Having said that, would it not be better to define what is loaded and when in our WordPress installation?

Create an index in the options table

There are several ways to thin the table options but we will see mainly one that has proven to be tremendously effective and easy to apply.

Why is the best solution?

The best solution to tables options huge that slow down your w eb is to create an index of the table options . In tests on sites with many lines with Autoload the difference between loading the table with and without index return some results beyond doubt.

In case you do not know English: in red loading time Autoload without index, in blue with index.

You can also see how the loading time in a site was reduced after adding an index:

The improvements performance and speed are brutal, especially with large databases, and the best thing is that creating an index is very simple.

Check the index

A good test to do before creating the index is to see how many columns (options) have Autoload in Yes that is, they are loaded. This would be done as follows:

For those that do not load, those that have Autoload in No .

Results will look like this:

The rule to apply would be the following:

  • If the values ​​in No exceed 60% of the total then you do not need an index.

Create an index with SQL commands

If you feel comfortable, you can create the index by executing a command like this:

Again, you can change the name of the index, autoloadindex in the example, and you must change the prefix of the table options by which you use.

Create an index with WP-CLI

If you prefer to use the WordPress command interface WP- CLI you would do it this way:

Create an index with plugins

In case you do not you want or do not you dare with SQL, there is also a plugin that will help you in this task of creating the index.

It is called Index Autoload and it does not even have adjustments, you activate it and it's done. [19659003] Of course, even if it is a plugin, the rule of backing up the database is still valid, do not forget it.

This plugin creates the Autoload index equally.

I do not want the index anymore

As there is nothing like freedom of choice, if at any time you no longer want the index you can execute this command to erase it:

Again, remember to change the index name and prefix of the table to yours.

If you used the option to do so with the plugin to deactivate it and delete it is also deleted the index.

Is it really worth it?

Although there is nothing better than doing a test, I will give you some advice:

  • If the options in Autoload of your table options weigh more than 1Mb worth it
  • If the Autoload Yes exceeds 60% it is worth it
  • If you have a table options great is worth it

The result is to reduce the WordPress queries to the table options in more than 90% in many cases, which results in better load times on your website. [19659138] To learn more

Here you have some resources to continue learning about this subject:

Car gando …

That may also help you:

Leave a Reply

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