[wp-trac] [WordPress Trac] #18536: Improve performance of WP_Query core

[WordPress Trac]

#18536: Improve performance of WP_Query core
-----------------------------+--------------------
 Reporter:  cheald           |       Owner:
     Type:  enhancement      |      Status:  new
 Priority:  normal           |   Milestone:  3.4
Component:  Performance      |     Version:  3.2.1
 Severity:  normal           |  Resolution:
 Keywords:  has-patch early  |
-----------------------------+--------------------

Old description:

> WP_Query, by default, selects wp_posts.* when building post queries. This
> can result in extremely large result sets when used with a large wp_posts
> table, resulting in MySQL being forced to use a temp table for sorting,
> which leads to all sorts of performance problems. SELECT * is generally
> considered to be a code smell as is, and is commonly known to cause
> problems at scale.
>
> This patch breaks default WP_Query queries into two parts - one to select
> a list of IDs with the given selection criteria, and a second to actually
> select the posts once the IDs have been determined. This vastly reduces
> the amount of data that has to be processed by the database, resulting in
> fewer context switches and vastly improved performance.
>
> I have implemented this in a production system with a wp_posts table with
> over 450k rows and reduced query times by two orders of magnitude.
> Database CPU burn is down by 90% and context switches have been
> drastically reduced.
>
> This is anecdotal, of course, but here's what this patch did for our DB
> master:
>
> [[Image(http://cl.ly/2j3a2a161m2131351j1w/Image_2011-08-27_at_9.15.23_PM.png)]]
>
> This should be of some benefit even in small cases, since smaller
> datasets will result in faster sorts. The overhead of the second query is
> minimal. In large cases, the performance benefits range from "good" to
> "dramatic".
>
> I have run the Wordpress PHPUnit test suite against the patch, and it did
> not break any tests. The suite, when I checked it out, had multiple
> failures present, but no new ones were introduced by this change.

New description:

 WP_Query, by default, selects wp_posts.* when building post queries. This
 can result in extremely large result sets when used with a large wp_posts
 table, resulting in MySQL being forced to use a temp table for sorting,
 which leads to all sorts of performance problems. SELECT * is generally
 considered to be a code smell as is, and is commonly known to cause
 problems at scale.

 This patch breaks default WP_Query queries into two parts - one to select
 a list of IDs with the given selection criteria, and a second to actually
 select the posts once the IDs have been determined. This vastly reduces
 the amount of data that has to be processed by the database, resulting in
 fewer context switches and vastly improved performance.

 I have implemented this in a production system with a wp_posts table with
 over 450k rows and reduced query times by two orders of magnitude.
 Database CPU burn is down by 90% and context switches have been
 drastically reduced.

 This should be of some benefit even in small cases, since smaller datasets
 will result in faster sorts. The overhead of the second query is minimal.
 In large cases, the performance benefits range from "good" to "dramatic".

 I have run the Wordpress PHPUnit test suite against the patch, and it did
 not break any tests. The suite, when I checked it out, had multiple
 failures present, but no new ones were introduced by this change.

--

Comment (by nacin):

 Removing the screenshot (dead link). I do remember the graph looking
 mighty impressive, though.

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