[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:  Awaiting Triage       
Component:  Performance  |     Version:  2.8.4                 
 Severity:  normal       |    Keywords:  has-patch tested early
-------------------------+--------------------------------------------------

Comment(by rowanbeentje):

 (query.5.diff has another serious problem - it leaves the standard
 postcount block beneath the modified code, so I think post counts are all
 off in it.)

 I've gone through the various patches here, and made some more fixes;
 we've published this to a couple of our live servers, and it's made a huge
 difference to slow queries being logged (and reduced page generation time
 a bit too).

 I'll attach two patches - one against 2.9.2 (which is what we're on, and
 what I'm testing properly), and one against trunk (which uses the same
 approaches, and which I've confirmed at least runs!).

 What's changed in these patches from the ones above?

  - The problems I've mentioned above regarding typos, syntax errors, and
 duplicated code have been fixed.
  - I like the idea of the extra argument being available for the filter
 hooks, so that's now available in the patches against trunk.
  - Problems with non-LIMITed queries have been resolved
  - Problems with certain query setups - meta setups, post statuses, author
 names, certain category queries - are resolved.  This includes the problem
 that willmot describes above on 25th Feb, but couldn't subsequently
 reproduce.
  - Problems with any queries involving GROUP BY have been fixed.

 As I've mentioned, applying the 2.9.2 patch is looking really good for us.
 Some SQL_CALC_FOUND_ROWS still seem to be appearing - for example a LIMIT
 0,1 which uses a filesort, I suspect unnecessarily - but this does improve
 the situation dramatically.

 (For those above wondering how to apply the patch against 2.9.2 - copy the
 patch into your wordpress install folder, then on the command line type
 "patch -p0 < query.php.2.9.2.patch".)

 I still have a few doubts about the setup of these patches, particularly
 the split between quick_placeholder and placeholder variables.  Denis, in
 terms of compatibility, what's the best way to structure these?  In my
 current version, the four quick_ equivalent variables are initially copied
 from their equivalent variables, before being passed into the filter
 hooks.  Is this still backwards compatible?

 It also results in the "fast" query looking like "SELECT * FROM …
 $quick_where AND $wpdb->posts.ID IN (…)".  The query optimiser should mean
 this isn't significantly slower than just a posts.ID IN query, but the
 $where clause will by default be copied into $quick_where...

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