[wp-trac] [WordPress Trac] #10964: Improving query_posts performance

[WordPress Trac]

#10964: Improving query_posts performance
-------------------------------------+-----------------------------
 Reporter:  buch0090                 |       Owner:
     Type:  enhancement              |      Status:  new
 Priority:  normal                   |   Milestone:  Future Release
Component:  Performance              |     Version:  2.8.4
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |
-------------------------------------+-----------------------------
Changes (by TonyTheGreat):

 * cc: TonyTheGreat (added)


Comment:

 1)  Lots of real world posts lack of true benchmarking it seems.
 http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-
 not-to-sql_calc_found_rows/ is an old example of a case with some quick
 testing.

 You take the example at the start if it's not sitting in memory it's going
 to be slow.  This is a problem with the larger datasets.  You use a count
 then a select you don't see the same sort of performance degradation with
 larger datasets due to the index lookup for the row count.

 We do web hosting and obviously a lot of our customers run wordpress.  95%
 of all slow queries (10s+) are wordpress and it's use of
 SQL_CALC_FOUND_ROWS queries.  All with datasets where post amounts are
 around 5000 or more.

 These queries are very problematic for users still using a shared
 environment.  It's unlikely their queries will always be in memory.  So as
 a result a lot more of the page loads are going to see slow load times
 from these queries not being in memory.

 For those not in shared environments they can still see issues if there is
 a lot of changes in the posts table causing it to be flushed from memory.

 2)  I really cannot comment on the problems it may cause.  Right now
 though leaving this problem is making wordpress look bad.  So needs to be
 addressed if it's going to require some plugins to make some changes so be
 it.  Longer it sits the more plugins it can potentially affect when it is
 changed.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/10964#comment:76>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software