[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:  new
 Priority:  normal                       |   Milestone:  Awaiting Review
Component:  Performance                  |     Version:  3.6
 Severity:  normal                       |  Resolution:
 Keywords:  has-patch reporter-feedback  |
-----------------------------------------+------------------------------
Changes (by pento):

 * keywords:  has-patch dev-feedback => has-patch reporter-feedback


Comment:

 There are a few things to consider here.

 First up, there's unlikely to be any benefit to adding `(option_name,
 option_value(50))` to the index. There are no queries in WordPress core
 that would use them.

 So, if we were going to add an index here, it would just be on `autoload`,
 which would have a very low cardinality. For some sites, this would
 potentially avoid a table scan, but I'm inclined to think that most sites
 would have >30% of rows with `autoload` set to `yes`.

 Anyway, to determine if this index would be helpful to you, and the degree
 of helpfulness, it would be most useful to see how long the query takes to
 run with and without the index, rather than looking at the number of rows
 read. (If you're running on a test machine, run the query at least twice,
 to ensure the cache is warmed up.

 If you're not using InnoDB, I'd also be curious to see how switching to
 that, with a large `innodb_buffer_pool_size`, changes the query time.

 Finally, if you're keeping stats on page generation time, I'd be
 interested to to see how this changes in production with an `autoload`
 index.

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