[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:  closed
 Priority:  normal              |   Milestone:
Component:  Options, Meta APIs  |     Version:  3.6
 Severity:  normal              |  Resolution:  maybelater
 Keywords:  has-patch           |     Focuses:  performance
--------------------------------+--------------------------

Comment (by JanR):

 Replying to [comment:41 fightthecurrent]:
 > Replying to [comment:11 nacin]:
 > > What's the purpose behind adding option_value to that index? It looks
 like it would only serve to increase the length of the index.
 > >
 > > What's the performance difference on these queries? Reducing rows
 examined from 1460 to 178 (or even 10,000 to 100) is still peanuts. DanBUK
 mentioned adding this index many times daily, but I am trying to figure
 out if that was simply cathartic or if it was actually making a
 difference.
 >
 > How about reducing 553,749 to 711?

 Nice graphs @fightthecurrent.

 We all know the performance gain - on InnoDB - with having an index on the
 autoload column of the wp_options table.

 The following is somewhat based on assumptions, and needs some
 investigation (it's early Sunday morning here...): nowadays at least most
 hosting providers are on MySQL version 5.5+, which has InnoDB as the
 default storage engine. The WordPress installer (`wp-admin/install.php`)
 already checks the MySQL version being used, and there is no engine
 definition in `wp-admin/includes/schema.php`. Thus making an extra check
 on MySQL storage engine quite possible:

 ITTT: If MySQL version >= 5.5 && storage engine = InnoDB, add an index on
 the autoload column.

 There is more than one way to lookup the storage engine:
 http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-
 type-for-a-specific-table. For plugins that require FULLTEXT indexes on
 table columns: MySQL >= 5.5.4 supports FULLTEXT indexes in InnoDB
 (http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html).

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