[wp-trac] [WordPress Trac] #10469: use of SQL_CALC_FOUND_ROWS needs to be eliminated

[WordPress Trac]

#10469: use of SQL_CALC_FOUND_ROWS needs to be eliminated
--------------------------+-------------------------------------------------
 Reporter:  _ck_          |       Owner:  ryan      
     Type:  defect (bug)  |      Status:  new       
 Priority:  normal        |   Milestone:  Unassigned
Component:  Query         |     Version:            
 Severity:  normal        |    Keywords:            
--------------------------+-------------------------------------------------

Comment(by _ck_):

 Except the queries I am seeing WordPress do with SQL_CALC_FOUND_ROWS are
 not significantly complex and do not typically use JOINS as meta is
 appended in a separate query, so COUNT() will still be faster in most
 cases.

 Typical example, benchmark it on a large site

 {{{
 SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE
 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish'
 OR wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date DESC
 LIMIT 25,25;

 SELECT FOUND_ROWS();
 }}}
 vs.

 {{{
 SELECT SQL_NO_CACHE COUNT(0) FROM wp_posts  WHERE 1=1  AND
 wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR
 wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date DESC
 }}}

 Typically COUNT(*) is also inferior to a single column COUNT(0) but my
 original workaround idea was a rush job.

 I assure you I am seeing SQL_CALC_FOUND_ROWS do bad things in a real-world
 environment in WordPress. Turn on the slow query log on any busy site and
 you'll see for yourself.

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