[wp-trac] [WordPress Trac] #14572: post_author_meta_box causes fatal error on site with large userbase.
WordPress Trac
wp-trac at lists.automattic.com
Mon Sep 6 19:16:09 UTC 2010
#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
More information about the wp-trac
mailing list