[buddypress-trac] [BuddyPress] #4647: Copy to tmp table

[buddypress-trac]

#4647: Copy to tmp table
--------------------------+-----------------------------
 Reporter:  elpix         |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Forums        |    Version:
 Severity:  critical      |   Keywords:
--------------------------+-----------------------------
 Hi all,

 I ask your help beacause I’ve already tried everything that I knew before
 to make a query work in few seconds

 I’m on WP 3.4.2, and BP 1.5.5

 Before, everything was working well, but one day, access to our forum
 became very very long (170 s average).
 After many tests, I think that it’s a cache problem.

 Here is two queries that make me think that :

 SELECT t.*, g.id AS object_id, g.name AS object_name, g.slug AS
 object_slug
 FROM wp_bb_topics AS t
 INNER JOIN wp_bb_posts p ON p.topic_id=t.topic_id AND
 p.post_time=t.topic_start_time
 JOIN wp_bp_groups AS g
 LEFT JOIN wp_bp_groups_groupmeta AS gm ON g.id = gm.group_id
 WHERE (gm.meta_key = 'forum_id' AND gm.meta_value = t.forum_id) AND
 g.STATUS = 'public' AND t.topic_status = '0'
 ORDER BY t.topic_time
 LIMIT 20

 And the same without the “LIMIT”

 SELECT t.*, g.id AS object_id, g.name AS object_name, g.slug AS
 object_slug
 FROM wp_bb_topics AS t
 INNER JOIN wp_bb_posts p ON p.topic_id=t.topic_id AND
 p.post_time=t.topic_start_time
 JOIN wp_bp_groups AS g
 LEFT JOIN wp_bp_groups_groupmeta AS gm ON g.id = gm.group_id
 WHERE (gm.meta_key = 'forum_id' AND gm.meta_value = t.forum_id) AND
 g.STATUS = 'public' AND t.topic_status = '0'
 ORDER BY t.topic_time

 The first (with LIMIT) take 150 secondes (At the beginning, it was working
 well).
 But the second (without LIMIT) takes 1.5 secones !!!!

 '''When I’m in PHPmyADMIN, the “show full processlist” tell “copy to tmp
 table” during the 150 secondes.'''

 As a precision !
 - In wp_bb_topics : 20.000 rows
 - In wp_bb_group_groupmeta : 75.000 rows


 Is there a cache limit ? I’ve reache with my DB (20.000 topics) ?
 Where I’ve to modified (I think in my.cnf), and what is the value to put
 (I’ve make a lot of test, without good results)?

 We tried to improve the buffers (all we can see in “show variable global”)
 but no results.

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/4647>
BuddyPress <http://buddypress.org/>
BuddyPress