wp_options and MySQL's "log-queries-not-using-indexes" config

[pkirk]

Usually both on production and dev servers MySQL's config parameter "log-queries-not-using-indexes" is activated.

This produce a lot of log entries like that:

# Time: 100710 11:53:28
# User@Host: xxx[xxx] @ localhost []
# Query_time: 0.010032  Lock_time: 0.000085 Rows_sent: 275  Rows_examined: 337
SET timestamp=1278755608;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Adding a simple index on the "autoload" field in wp_options table resolve the "problem".

[goatherd]

What got me irritated too is the fact that autoload seems to be a boolean type (yes/no) yet is defined as varchar(20) of default database charset wasting up to 7 Bytes per row (for utf8_*).

It could be just as well defined as enum('yes', 'no') for single-byte storage and smaller index.

Also in some cases (slow machine, many options) the query reached well above 0.1 seconds without index.

[pento]

Adding an index won't improve performance for this. If a query going through an index reads more than 30% of the rows, MySQL will revert to a table scan.

There would be no benefit to switching to enum. Even assuming a ludicrously massive wp_options table of 5000 rows, the space saving would still be less than a single InnoDB page.

[nacin]

Works for me.

[nigro.simone]

On MariaDB 10.1.9

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
(262 total, Query took 0.0156 seconds.)

---

CREATE INDEX autoload ON wp_options(autoload, option_name);
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
(262 total, Query took 0.0000 seconds.)

[SergeyBiryukov]

Follow-up: #24044

[arjenlentz]

Replying to pento:

Adding an index won't improve performance for this. If a query going through an index reads more than 30% of the rows, MySQL will revert to a table scan.

Sorry pento, but this is not correct. It is a neat rule of thumb that we've used in training also, but there is no such rule in the code itself. (feel free to try and prove me wrong, show me a relevant MySQL server source code snippet - hint: I used to work for MySQL).

What's relevant is the selectivity, and having an index on a boolean can make a significant difference - more so in more recent versions of MySQL and MariaDB.

But let's just check this in the real world, as that's what matters:

> 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 | 60453 | Using where |
+------+-------------+------------+------+---------------+------+---------+------+-------+-------------+


> ALTER TABLE wp_options ADD INDEX (autoload);
 

> explain SELECT option_name, option_value FROM wp_options WHERE autoload = 'no';
+------+-------------+------------+------+---------------+----------+---------+-------+-------+-----------------------+
| id   | select_type | table      | type | possible_keys | key      | key_len | ref   | rows  | Extra                 |
+------+-------------+------------+------+---------------+----------+---------+-------+-------+-----------------------+
|    1 | SIMPLE      | wp_options | ref  | autoload      | autoload | 82      | const | 30228 | Using index condition |
+------+-------------+------------+------+---------------+----------+---------+-------+-------+-----------------------+
1 row in set (0.00 sec)

 
> 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 | 82      | const | 2329 | Using index condition |
+------+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

As you can see, there is quite a difference with an index, particularly for the 'yes' case.
And in both cases the server does choose to use the index rather than do a tablescan - so the optimiser thinks that it's more beneficial.

Now, mind that with the InnoDB storage engine a secondary index (such as autoload) causes a double lookup. That is, once it finds an entry in such an index, it has a pointer to the primary key where it then has to look up the rest of the row. So there is a cost to using a secondary index (rather than scanning the table using the clustered PRIMARY KEY), and the optimiser knows it.

With this in mind, I think we can safely say that having the index makes sense. It may not make a huge difference per query, but WP does a lot of queries and thus every optimisation helps.

There would be no benefit to switching to enum. Even assuming a ludicrously massive wp_options table of 5000 rows, the space saving would still be less than a single InnoDB page.

Indeed, an ENUM would not help.
But an index does. Please add it. Cheap gain!

[dd32]

Please keep all discussion on one open ticket only, in this case, the latest ticket for this is #24044.