[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  |
-------------------------------------+-----------------------------

Comment (by bear_beavis):

 I also got some performance issues on a site.
 I wanted to share some tests i have made.

 The query is :
 {{{
 SELECT SQL_CALC_FOUND_ROWS  posts.* FROM posts  INNER JOIN
 term_relationships ON (lme_posts.ID = term_relationships.object_id) WHERE
 1=1  AND ( term_relationships.term_taxonomy_id IN
 (9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55)
 ) AND psots_post_type = 'post' AND (posts.post_status = 'publish') GROUP
 BY lme_posts.ID ORDER BY lme_posts.post_date DESC LIMIT 0, 10;
 }}}

 SQL_CALC_FOUND_ROWS is not really the problem, the problem is that mysql
 is creating a temporary table with 200k rows on disk.

 When you do the same request with
 {{{
 SELECT SQL_CALC_FOUND_ROWS  posts.ID
 }}}
 instead of
 {{{
 SELECT SQL_CALC_FOUND_ROWS  posts.*
 }}}
 You can see that the temporary table is created in Ram instead of disk.
 Because posts table contains columns with text fields, mysql always create
 temporary tables with posts.* on disk.

 * You can verify this, by doing before and after your query :
 {{{
 mysql> show global status like 'Created_tmp_disk_tables';
 }}}
 For this test to be effective :
 * add SQL_NO_CACHE to your query to ensure you're not using query_cache.
 * ensure tmp_table_size and max_heap_table_size are sized to permit
 temporary table to fit in ram.
 * Of course don't do that on loaded mysqld server, you should be the only
 one using the server for this test to be valid

 You'll see that Created_tmp_disk_tables value will not increase using
 posts.ID instead of posts.*

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