[wp-trac] [WordPress Trac] #24044: Add index to wp_options to aid/improve performance

[WordPress Trac]

#24044: Add index to wp_options to aid/improve performance
--------------------------------+------------------------------
 Reporter:  DanBUK              |       Owner:
     Type:  enhancement         |      Status:  reopened
 Priority:  normal              |   Milestone:  Awaiting Review
Component:  Options, Meta APIs  |     Version:
 Severity:  major               |  Resolution:
 Keywords:  has-patch           |     Focuses:  performance
--------------------------------+------------------------------

Comment (by arjenlentz):

 Having an index on a boolean column can make a significant difference -
 more so in more recent versions of MySQL and MariaDB.

 Let's just check this in the real world, as that's what matters (data from
 a live server):

 {{{
 > 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.


 It is absolutely true that having more indexes slightly slows down writes,
 but that's not an argument against having indexes as such - it's an
 argument in having ineffective (superfluous) indexes.
 This one is clearly useful. And since most operations on this table are in
 fact reads, not writes, the balance is really seriously in favour of
 having the index (as also indicated by the EXPLAIN output above).

 In MyISAM the gains would not necessarily be the same, however it will
 benefit. I would also note that any new install now will use InnoDB, as
 that has been the default engine in MySQL and MariaDB for some years
 already now. Granted, old installed may well still be using MyISAM, but if
 those installs cared for performance (or scaled up) they'd have done
 something about the engine they use (ALTER TABLE ... ENGINE=INNODB is all
 that's needed for that) as well as some decent server tuning. It's not
 something we want to be worrying about here, pandering to an archaic
 lowest denominator hurts users now.
 Thanks

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