[wp-trac] [WordPress Trac] #14258: wp_options and MySQL's "log-queries-not-using-indexes" config

[WordPress Trac]

#14258: wp_options and MySQL's "log-queries-not-using-indexes" config
--------------------------+--------------------------
 Reporter:  pkirk         |       Owner:
     Type:  defect (bug)  |      Status:  reopened
 Priority:  normal        |   Milestone:
Component:  Database      |     Version:  4.7.2
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  performance
--------------------------+--------------------------
Changes (by arjenlentz):

 * status:  closed => reopened
 * type:  enhancement => defect (bug)
 * version:   => 4.7.2
 * resolution:  wontfix =>


Comment:

 Replying to [comment:3 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!

--
Ticket URL: <https://core.trac.wordpress.org/ticket/14258#comment:8>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform