WordPress wp_options table autoload micro-optimization

Learn how to optimize WordPress’ wp_options table for faster database results. This probably isn’t a big issue, but today I noticed a slow MySQL query coming from a WordPress database (WordPress wp_options table). This made me investigate and to optimize the WordPress wp_options table “autoload” feature. The autoload feature loads and caches all autoloaded options, if available, or all options. The default option is to autoload, and over time when the wp_options table grows, this degrades performance.

How to identify a slow WordPress query on wp_options #

Even though our MySQL servers are heavily InnoDB optimized, I noticed the following slow query in our slow query log file. It’s related to the WordPress wp_options table:

# Time: 140502 12:17:44
# User@Host: db-name[db-name] @ webserver.example.com [1.1.1.11]
# Query_time: 3.491801  Lock_time: 0.000049 Rows_sent: 1337  Rows_examined: 33927
SET timestamp=1399025864;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

So that is almost 3.5 seconds to examine 33927 and send 1337 rows…

The wp_options table description is as follows:

mysql> desc wp_options;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(64)         | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   | MUL | yes     |                |
+--------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

The only values for autoload are ‘yes‘ and ‘no‘, so why a varchar(20) field type? Oh well, that’s a whole other issue…

I thought you might find this interesting:   Tips to speed up WordPress, serve gzip compressed static HTML files

Examining the slow autoload query in WordPress #

You can use MySQL’s EXPLAIN to examine queries and their structure. MySQL EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).

While examining the autoload-query, you’ll notice that there is no index present on the autoload column:

mysql> explain SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | wp_options | ALL  | NULL          | NULL | NULL    | NULL | 33927 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Again the 33927 rows.

Optimize wp_options with an autoload index #

You can optimize the WordPress wp_options table simply by adding an index:

mysql> ALTER TABLE `wp_options` ADD INDEX (`autoload`);
Query OK, 33927 rows affected (0.70 sec)
Records: 33927  Duplicates: 0  Warnings: 0

and we examine the same query again:

mysql> explain SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | wp_options | ref  | autoload      | autoload | 62      | const | 1508 | Using where |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Now there are only 1508 rows left!

It’s important to optimize MySQL with indices, and how to improve InnoDB performance in MySQL.

On Make WordPress Core Trac, multiple tickets exist requesting this index to be added in WordPress Core:


Please Support Saotn.org

Each post on Sysadmins of the North takes a significant amount of time to research, write, and edit. Therefore, your donation helps a lot! For example, a donation of $3 U.S. buys me a cup of coffee, and as you know: things jsut work better with coffee. A $10 U.S. donation buys me one month of web hosting (yes, hosting costs money). But seriously, thank you for any amount. Much appreciated!

I thought you might find this interesting:   "10+ useful SQL queries to clean up your WordPress database"

Please donate to support this site if you found a post interesting or if it helped you solve a problem. Thanks! (Tip: no Paypal account required)

If you appreciated this post, then please donate using this Paypal button


Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization.

Leave a Reply

4 Comments on "WordPress wp_options table autoload micro-optimization"

Hi! Join the discussion, leave a reply!

Sort by:   newest | oldest | most voted
jbarrywebtest
Guest

This helped me pinpoint and resolve a major issue we were having with slow queries on wp_options. Much Appreciated!

Daniel
Guest

I got this query repeating itself in mysql-slow.log and I cannot figure out what it is (cannot find much on it on the web either):
UPDATE `wp_options` SET `option_value` = ‘150’ WHERE `option_name` = ‘thumbnail_size_w’;
# Query_time: 10.893515 Lock_time: 0.000031 Rows_sent: 0 Rows_examined: 1

It keeps repeating itself obscuring anything else and slowing down the mysql server.

Any ideas on how to fix this?

Thanks!

Sneakers4u
Guest

You are a gift to the world. Solved many deadlocks for me. Thank you, thank you!