[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:  Options and Meta             |     Version:  3.6
 Severity:  normal                       |  Resolution:
 Keywords:  has-patch reporter-feedback  |     Focuses:  performance
-----------------------------------------+------------------------------

Comment (by DanBUK):

 Sorry I never came back with statistics. I'll try and find some of the
 customer sites I've worked on and generate some data.

 Replying to [comment:13 pento]:
 > 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.

 In almost all cases everyone should be using InnoDB for a WP database,
 excluding the tables that have FULLTEXT indexes of course. (Then again,
 full text indexing should really be shifted to something like Solr, but
 that is another story...) The nature of the wp_comments especially cannot
 scale with MyISAM, the write level of a busy WP site means that you'll end
 up with table locks. And the number of plugins that utilise wp_meta or
 wp_options to store post views, or similar that get updated on every
 view... Again leads to locking that blocks the viewing of the site.

 Just by having a large buffer pool that doesn't change how the MySQL query
 planner/statistic collator/table scanning works. It's not about if the
 data is in RAM or not, the wp_options table even in the cases I've seen
 with up to 10k rows, isn't actually a lot of data, and will either end up
 in the buffer pool or the OS file system caches. The issue is that the
 planning has to examine all the rows in the table, every time the page is
 loaded. That's dependent upon CPU time.

 Heck you can put your buffer pool up to 5x your database size and you'll
 see 0 performance improvement over 1x. Ideally the buffer pool should be
 as big as the dataset that is accessed often. Ergo if you have a 10GB
 InnoDB dataset and 9GB of that is archive of logs/actions/very old
 posts/etc  you only really need a 1GB buffer pool.

 But that is the thing, we are moving into the realm of database
 optimisation, and to be honest from my view that isn't always seen by
 developers. Don't even get me started on Magento.

 Replying to [comment:20 nacin]:
 > You should enable an external object cache, that way transients aren't
 stored in the database. If the lack of an index on this table forces
 hosting companies to consider the availability of an external object cache
 for their users, I'm not necessarily going to be bothered by that.

 Quite often the hosting company cannot control the customer fully. The
 customer may have limited budget and we work within their constraints.

 Also there are lots of plugins out there that are already using
 transients, most users of WP that are not developers/sys admins/etc do not
 understand that maybe they have used the inappropriate method of storing
 data.

 Replying to [comment:20 nacin]:
 > I find it hard to justify an index that benefits an infinitesimal number
 of sites, probably slows down many others, especially were the sites
 affected have better steps to take to improve performance.

 How do you think this will slow things down? It is an index of boolean
 cardinality, therefore the binary tree will have only two branches
 (YES|NO). The addition/updating of rows only require very small index
 changes. I really cannot understand how you think it will slow things
 down.

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