[wp-trac] [WordPress Trac] #14572: post_author_meta_box causes fatal error on site with large userbase.

[WordPress Trac]

#14572: post_author_meta_box causes fatal error on site with large userbase.
----------------------------+-----------------------------------------------
 Reporter:  tomdebruin      |       Owner:  scribu          
     Type:  defect (bug)    |      Status:  assigned        
 Priority:  normal          |   Milestone:  3.1             
Component:  Administration  |     Version:  3.0.1           
 Severity:  normal          |    Keywords:  needs-patch gsoc
----------------------------+-----------------------------------------------

Comment(by mdawaffe):

 Replying to [comment:23 scribu]:
 > Why is that a dependent subquery? I don't get it.

 Because the MySQL optimizer is naive in this situation:
 http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html

 You can force the inner query to be executed first by doing something
 like:

 {{{
 EXPLAIN
 SELECT DISTINCT(minibb_users.ID) FROM minibb_users
 WHERE 1=1 AND minibb_users.ID IN (
   SELECT user_id FROM (
     SELECT user_id FROM minibb_usermeta
     WHERE CASE meta_key
       WHEN 'wporg_5_capabilities' THEN meta_value IS NOT NULL
     END
     GROUP BY user_id HAVING COUNT(*) = 1
   ) AS foo
 ) ORDER BY user_login ASC;
 }}}

 || id || select_type || table || type || possible_keys || key || key_len
 || ref || rows || Extra ||
 || 1 || PRIMARY || minibb_users || index || NULL || user_login || 60 ||
 NULL || 1254992 || Using where; Using index ||
 || 2 || DEPENDENT SUBQUERY || <derived3> || ALL || NULL || NULL || NULL ||
 NULL || 10 || Using where ||
 || 3 || DERIVED || minibb_usermeta || index || NULL || user_id || 8 ||
 NULL || 5830942 || Using where ||

 The first subquery is still "dependent", but now it's only operating on a
 small number of rows the derived table returns.

 A JOIN is simpler, though.

 The other problem with this query is the usermeta portion (whether it's in
 a subquery or in a join).

 {{{
 EXPLAIN
 SELECT * FROM wp_usermeta
 WHERE CASE meta_key
   WHEN 'wp_capabilities' THEN meta_value IS NOT NULL
 END
 }}}

 || id || select_type || table || type || possible_keys || key || key_len
 || ref || rows || Extra ||
 || 1 || SIMPLE || wp_usermeta || ALL || NULL || NULL || NULL || NULL || 59
 || Using where ||

 That can't be indexed.  It has to look at each row to see if it matches
 one of (in this case, the only) case statement.

 in _wp_meta_sql(), there's no way for the logic to result in multiple
 $clauses, so why not just go with:

 {{{
 EXPLAIN
 SELECT * FROM wp_usermeta WHERE meta_key = 'wp_capabilities'
 }}}

 || id || select_type || table || type || possible_keys || key || key_len
 || ref || rows || Extra ||
 || 1 || SIMPLE || wp_usermeta || ref || meta_key || meta_key || 768 ||
 const || 2 || Using where ||

 Also, I don't understand the point of the {{{GROUP BY user_id HAVING
 COUNT(*) = 1}}}.  When would there ever be multiple wp_capabilities meta
 rows for a single user_id?  Even if there are, why does it matter?  That
 user_id would still be a member of the blog.

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