[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 Denis-de-Bernardy):

 Replying to [comment:33 scribu]:
 > (In [15581]) Use _wp_meta_sql() in WP_Query. See #14572. See #14645

 I'm not 100% this is an enhancement when dealing with multiple criteria.
 SQL optimizers tend to go nuts once the number of joins is high, picking a
 random plan among several options, and then working out which one is the
 better one through a genetic algorithm.

 This:

 {{{
 SELECT DISTINCT(minibb_users.ID) FROM minibb_users
 JOIN (
   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 userquery ON minibb_users.ID = userquery.user_id
 ORDER BY user_login ASC
 }}}

 ... has a group by clause in the subquery, which very much guarantees that
 the query rewriter will never rewrite the query such as the following:

 {{{
 SELECT DISTINCT(minibb_users.ID) FROM minibb_users
 JOIN minibb_usermeta
 ON minibb_users.ID = minibb_usermeta.user_id
 AND CASE minibb_usermeta.meta_key
     WHEN 'wporg_5_capabilities' THEN minibb_usermeta.meta_value IS NOT
 NULL
     -- extra WHEN clauses can go here
   END
 GROUP BY user_id HAVING COUNT(*) = 1
 ORDER BY user_login ASC
 }}}

 The above rewrite should allow to do a single join on an indexed column
 (supposedly user_id is indexed in usermeta? if not, it needs to be),
 rather than as many joins as there are criteria.

 The group by and having clauses also can be dropped entirely, when a
 unique meta criteria is considered. As can the CASE statement:

 {{{
 SELECT DISTINCT(minibb_users.ID) FROM minibb_users
 JOIN minibb_usermeta
 ON minibb_users.ID = minibb_usermeta.user_id
 AND minibb_usermeta.meta_key = 'wporg_5_capabilities'
 AND minibb_usermeta.meta_value IS NOT NULL
 ORDER BY user_login ASC
 }}}

 Other aside, instead of minibb_usermeta.meta_value IS NOT NULL, we could
 also write TRUE, since the column is never null anyway.

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