[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:  Awaiting Review
Component:  Query        |     Version:  3.2.1
 Severity:  normal       |  Resolution:
 Keywords:  has-patch    |
-------------------------+------------------------------

Comment (by cheald):

 The problem is indeed the tablesorts. This patch helps keep our queries
 out of tablesort territory by reducing the amount of data that the DB has
 to chew on. Here's a chunk from our slow query log:


 {{{
 # User at Host: prod[prod] @  [192.168.100.162]
 # Thread_id: 213317196  Schema: prod  Last_errno: 0  Killed: 0
 # Query_time: 0.542673  Lock_time: 0.000094  Rows_sent: 2  Rows_examined:
 47995  Rows_affected: 0  Rows_read: 9108
 # Bytes_sent: 10754  Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes:
 50918492
 # InnoDB_trx_id: 676FA764
 # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes
 Tmp_table_on_disk: Yes
 # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0
 #   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
 #   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
 #   InnoDB_pages_distinct: 10030
 SET timestamp=1302277759;
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN
 wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
 INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id =
 wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_posts.ID NOT IN
 (332000,583467,583371,579313,583657,583185) AND wp_term_taxonomy.taxonomy
 = 'post_tag'  AND wp_term_taxonomy.term_id IN ('1862', '82', '19567',
 '553', '135', '43', '1174', '1100', '5819', '16796', '119', '74')  AND
 wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP
 BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 2;
 }}}

 And here's the EXPLAIN:

 {{{
 +----+-------------+-----------------------+--------+--------------------------------------+------------------+---------+----------------------------------------+------+-----------------------------------------------------------+
 | id | select_type | table                 | type   | possible_keys
 | key              | key_len | ref                                    |
 rows | Extra                                                     |
 +----+-------------+-----------------------+--------+--------------------------------------+------------------+---------+----------------------------------------+------+-----------------------------------------------------------+
 |  1 | SIMPLE      | wp_term_taxonomy      | range  |
 PRIMARY,term_id_taxonomy,taxonomy    | term_id_taxonomy | 106     | NULL
 |   12 | Using where; Using index; Using temporary; Using filesort |
 |  1 | SIMPLE      | wp_term_relationships | ref    |
 PRIMARY,term_taxonomy_id             | term_taxonomy_id | 8       |
 prod.wp_term_taxonomy.term_taxonomy_id |   15 | Using index
 |
 |  1 | SIMPLE      | wp_posts              | eq_ref |
 PRIMARY,post_status,type_status_date | PRIMARY          | 8       |
 prod.wp_term_relationships.object_id   |    1 | Using where
 |
 +----+-------------+-----------------------+--------+--------------------------------------+------------------+---------+----------------------------------------+------+-----------------------------------------------------------+
 }}}

 Nothing too fancy going on there,
 The really interesting thing there is the temp table size - 50,918,492
 bytes - just to get 2 posts! "Tmp_table_on_disk" explains the performance
 pretty easily - making the context switch to write the temp table isn't
 trivial!

 Here's another:

 {{{

 # User at Host: prod[prod] @  [192.168.100.162]
 # Thread_id: 213303997  Schema: prod  Last_errno: 0  Killed: 0
 # Query_time: 0.909574  Lock_time: 0.000117  Rows_sent: 2  Rows_examined:
 33733  Rows_affected: 0  Rows_read: 6785
 # Bytes_sent: 11761  Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes:
 40452708
 # InnoDB_trx_id: 676ED8D4
 # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes
 Tmp_table_on_disk: Yes
 # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0
 #   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
 #   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
 #   InnoDB_pages_distinct: 7863
 SET timestamp=1302277444;
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN
 wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
 INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id =
 wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_posts.ID NOT IN
 (249519,583467,583371,579313,583657,583185) AND wp_term_taxonomy.taxonomy
 = 'post_tag'  AND wp_term_taxonomy.term_id IN ('20747', '2009', '82',
 '131', '553', '135', '1156', '732', '1100', '119')  AND wp_posts.post_type
 = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER
 BY wp_posts.post_date DESC LIMIT 0, 2;
 }}}

 Explained:

 {{{

 +----+-------------+-----------------------+--------+--------------------------------------+------------------+---------+----------------------------------------+------+-----------------------------------------------------------+
 | id | select_type | table                 | type   | possible_keys
 | key              | key_len | ref                                    |
 rows | Extra                                                     |
 +----+-------------+-----------------------+--------+--------------------------------------+------------------+---------+----------------------------------------+------+-----------------------------------------------------------+
 |  1 | SIMPLE      | wp_term_taxonomy      | range  |
 PRIMARY,term_id_taxonomy,taxonomy    | term_id_taxonomy | 106     | NULL
 |   10 | Using where; Using index; Using temporary; Using filesort |
 |  1 | SIMPLE      | wp_term_relationships | ref    |
 PRIMARY,term_taxonomy_id             | term_taxonomy_id | 8       |
 prod.wp_term_taxonomy.term_taxonomy_id |   15 | Using where; Using index
 |
 |  1 | SIMPLE      | wp_posts              | eq_ref |
 PRIMARY,post_status,type_status_date | PRIMARY          | 8       |
 prod.wp_term_relationships.object_id   |    1 | Using where
 |
 +----+-------------+-----------------------+--------+--------------------------------------+------------------+---------+----------------------------------------+------+-----------------------------------------------------------+
 }}}

 All the patch does is reduce the amount of data that MySQL has to manage
 to perform the sort - with small dataset sizes, this isn't likely to have
 much of an impact, but once the size of your potential resultset (with
 included post content) passes your query into filesort territory, the
 differences become *big*.

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