[buddypress-trac] [BuddyPress] #4041: Use SELECT FOUND_ROWS() for 'total' queries instead of a second query

[buddypress-trac at lists.automattic.com]

#4041: Use SELECT FOUND_ROWS() for 'total' queries instead of a second query
----------------------------+-----------------------
 Reporter:  boonebgorges    |       Owner:  DJPaul
     Type:  defect (bug)    |      Status:  assigned
 Priority:  normal          |   Milestone:  1.6
Component:  All Components  |     Version:
 Severity:  normal          |  Resolution:
 Keywords:                  |
----------------------------+-----------------------

Comment (by webraket):

 When I used SQL_CALC_FOUND_ROWS, that query ended up multiple times in
 slow-query.log
 The avg. server-load spiked around 4.0

 {{{
 # Query_time: 20.422221  Lock_time: 0.000089 Rows_sent: 20  Rows_examined:
 1836712
 SELECT SQL_CALC_FOUND_ROWS a.*, u.user_email, u.user_nicename,
 u.user_login, u.display_name  FROM wp_bp_activity a LEFT JOIN wp_users u
 ON a.user_id = u.ID WHERE a.hide_sitewide = 0 AND a.type !=
 'activity_comment' ORDER BY a.date_recorded DESC LIMIT 0, 20;

 # Query_time: 13.388473  Lock_time: 0.013897 Rows_sent: 20  Rows_examined:
 1836778
 SELECT SQL_CALC_FOUND_ROWS a.*, u.user_email, u.user_nicename,
 u.user_login, u.display_name  FROM wp_bp_activity a LEFT JOIN wp_users u
 ON a.user_id = u.ID WHERE a.hide_sitewide = 0 AND a.type !=
 'activity_comment' ORDER BY a.date_recorded DESC LIMIT 0, 20;
 }}}

 1.8million rows examined seems way too much for 600.000 activity items I
 currently have.
 Maybe SQL_CALC_FOUND_ROWS performs the wp_user JOIN for al activity items
 it needs to count?

 Phpmyadmin shows "using where;using filesort;" with EXPLAIN SELECT...
 Once I remove (SQL_CALC_FOUND_ROWS) or (ORDER BY a.date_recorded DESC) it
 only shows "using where;"

 I went back to the count() query, resulting in server-load around 0.5 -
 1.0 ;)
 It's still not optimal however:

 {{{
 # Query_time: 15.334062  Lock_time: 0.000034 Rows_sent: 1  Rows_examined:
 613581
 SELECT count(a.id) FROM wp_bp_activity a WHERE a.hide_sitewide = 0 AND
 a.type != 'activity_comment';
 }}}

 PS: I also see the WP_QUERY using SQL_CALC_FOUND_ROWS in slow-query.log
 Many Wordpress users reported problems with it:
 http://core.trac.wordpress.org/ticket/10469
 http://core.trac.wordpress.org/ticket/10964

 {{{
 # Query_time: 10.427559  Lock_time: 0.106090 Rows_sent: 10  Rows_examined:
 90
 SELECT SQL_CALC_FOUND_ROWS  wp_12958_posts.* FROM wp_12958_posts  WHERE
 1=1  AND wp_12958_posts.post_type = 'post' AND (wp_12958_posts.post_status
 = 'publish')  ORDER BY wp_12958_posts.post_date DESC LIMIT 0, 10;

 # Query_time: 12.887792  Lock_time: 0.020614 Rows_sent: 10  Rows_examined:
 12
 SELECT SQL_CALC_FOUND_ROWS  wp_23379_posts.* FROM wp_23379_posts  WHERE
 1=1  AND wp_23379_posts.post_type = 'post' AND (wp_23379_posts.post_status
 = 'publish')  ORDER BY wp_23379_posts.post_date DESC LIMIT 0, 10;
 }}}

 NOTE: These test results come directly from a busy production server.
 Someone should test it in a local environment to ensure query's are not
 locking up each other to get the best measurements possible.

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/4041#comment:5>
BuddyPress <http://buddypress.org/>
BuddyPress