[wp-trac] [WordPress Trac] #23849: Unexepected results when running WP_User_Query with role and meta_query

[WordPress Trac]

#23849: Unexepected results when running WP_User_Query with role and meta_query
--------------------------+------------------------------
 Reporter:  layotte       |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Query         |     Version:
 Severity:  normal        |  Resolution:
 Keywords:  dev-feedback  |
--------------------------+------------------------------

Comment (by wonderboymusic):

 I just ran this query against my local database with 1.2 million users in
 it - I cancelled the query after 2 minutes of it with no end in sight.
 We really need to not store role and caps in serialized arrays. We only
 get away with it because most people only have a few users. At scale it
 all falls apart.

 {{{
 SELECT DISTINCT SQL_CALC_FOUND_ROWS 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)
 INNER JOIN wp_usermeta AS mt2 ON (wp_users.ID = mt2.user_id)
 WHERE 1=1
 AND (wp_usermeta.meta_key = 'first_name'
         OR  (mt1.meta_key = 'first_name' AND CAST(mt1.meta_value AS CHAR)
 NOT LIKE '%off%')
         OR  (mt2.meta_key = 'wp_capabilities' AND CAST(mt2.meta_value AS
 CHAR) LIKE '%\"Author\"%') )
 ORDER BY user_login ASC LIMIT 100
 }}}

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