[wp-trac] [WordPress Trac] #17582: Problems with duplicated users

[WordPress Trac]

#17582: Problems with duplicated users
--------------------------+---------------------
 Reporter:  scribu        |       Owner:  ryan
     Type:  defect (bug)  |      Status:  closed
 Priority:  normal        |   Milestone:  3.2
Component:  Query         |     Version:
 Severity:  normal        |  Resolution:  fixed
 Keywords:  has-patch     |
--------------------------+---------------------

Comment (by greuben):

 > If you order by display_name, the group by/order by should be:
 > {{{
 > GROUP BY display_name, ID
 > ORDER BY display_name, ID
 > }}}
 > That way they'll only need to be ordered once, and the extra ID sub-
 ordering will strip out the dups (at a negligible cost).

 Here's some more profiling info based on the suggested query & query with
 patch .2.diff
 {{{
 #!sql
 +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Query_ID | Duration   | Query
 |
 +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 |        1 | 0.01018900 | SELECT wp_users.* FROM wp_users INNER JOIN
 wp_usermeta ON (wp_users.ID = wp_usermeta.user_id) INNER JOIN wp_usermeta
 AS mt1 ON (wp_users.ID = mt1.user_id) group by display_name, ID ORDER BY
 display_name, ID ASC |
 |        2 | 0.00065200 | SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_users.*
 FROM wp_users INNER JOIN wp_usermeta ON (wp_users.ID =
 wp_usermeta.user_id) INNER JOIN wp_usermeta AS mt1 ON (wp_users.ID =
 mt1.user_id) ORDER BY display_name ASC  |
 |        3 | 0.01074400 | SELECT wp_users.* FROM wp_users INNER JOIN
 wp_usermeta ON (wp_users.ID = wp_usermeta.user_id) INNER JOIN wp_usermeta
 AS mt1 ON (wp_users.ID = mt1.user_id) group by display_name, ID ORDER BY
 display_name, ID ASC |
 |        4 | 0.00065100 | SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_users.*
 FROM wp_users INNER JOIN wp_usermeta ON (wp_users.ID =
 wp_usermeta.user_id) INNER JOIN wp_usermeta AS mt1 ON (wp_users.ID =
 mt1.user_id) ORDER BY display_name ASC  |
 +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 }}}

 I am not sure about the theory but DISTINCT is faster in this case.

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