[buddypress-trac] [BuddyPress] #5210: Using DISTINCT in the select statement when retrieving activity data slows down the query

[buddypress-trac]

#5210: Using DISTINCT in the select statement when retrieving activity data slows
down the query
-------------------------------+------------------------------
 Reporter:  kwerri             |       Owner:
     Type:  enhancement        |      Status:  new
 Priority:  normal             |   Milestone:  Awaiting Review
Component:  Activity           |     Version:  1.8.1
 Severity:  normal             |  Resolution:
 Keywords:  reporter-feedback  |
-------------------------------+------------------------------

Comment (by kwerri):

 OK, here is more info:

 I ran this query:


 {{{
 EXPLAIN SELECT DISTINCT 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.is_spam = 0 AND a.hide_sitewide = 0 AND a.type !=
 'activity_comment' ORDER BY a.date_recorded DESC LIMIT 0, 20;
 }}}

 I got this:

 {{{
 +----+-------------+-------+--------+--------------------+---------------+---------+--------------------------+-------+----------------------------------------------+
 | id | select_type | table | type   | possible_keys      | key           |
 key_len | ref                      | rows  | Extra
 |
 +----+-------------+-------+--------+--------------------+---------------+---------+--------------------------+-------+----------------------------------------------+
 |  1 | SIMPLE      | a     | ref    | type,hide_sitewide,| hide_sitewide |
 2       | const                    | 97582 | Using where; Using temporary;
 Using filesort |
 |    |             |       |        | is_spam            |               |
 |                          |       |
 |
 |  1 | SIMPLE      | u     | eq_ref | PRIMARY            | PRIMARY       |
 8       | mydb.a.user_id           |     1 |
 |
 +----+-------------+-------+--------+--------------------+---------------+---------+--------------------------+-------+----------------------------------------------+
 }}}


 I also did a profile of the query - this is what I got:

 {{{
 +----------------------+-----------+
 | Status               | Duration  |
 +----------------------+-----------+
 | starting             |  0.000099 |
 | checking permissions |  0.000007 |
 | checking permissions |  0.000006 |
 | Opening tables       |  0.000023 |
 | System lock          |  0.000011 |
 | init                 |  0.000049 |
 | optimizing           |  0.000018 |
 | statistics           |  0.000153 |
 | preparing            |  0.000027 |
 | Creating tmp table   |  0.000386 |
 | executing            |  0.000006 |
 | Copying to tmp table | 17.143788 |
 | Sorting result       |  0.516267 |
 | Sending data         |  0.000156 |
 | end                  |  0.000005 |
 | removing tmp table   |  0.374855 |
 | end                  |  0.000015 |
 | query end            |  0.000007 |
 | closing tables       |  0.000014 |
 | freeing items        |  0.000033 |
 | logging slow query   |  0.000003 |
 | logging slow query   |  1.447473 |
 | cleaning up          |  0.000023 |
 +----------------------+-----------+
 }}}

 Note the 17-second duration to takes to copy to the tmp table.

 I have 2GB of RAM on my server, and I'm using mysql 5.5.32.

 Lastly, there's this:

 http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

 The relevant section is here:

 "''Temporary tables can be created under conditions such as these:''

 ''If there is an ORDER BY clause and a different GROUP BY clause, or if
 the ORDER BY or GROUP BY contains columns from tables other than the first
 table in the join queue, a temporary table is created.''

 '''''DISTINCT combined with ORDER BY may require a temporary table'''.''"

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