Syntax for combining joins in mysql
I seem to remember reading somewhere that there is a way to combine LEFT JOIN statements into a more simple query.开发者_如何学C I have the following query and was hoping someone could kindly take a look at it.
SET @userId = 8;
SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches
FROM user_goal ug, user u, profile p
LEFT JOIN user_block ub ON @userId = ub.blocked_id
LEFT JOIN user_block ub2 ON @userId = ub2.blocker_id
LEFT JOIN user_user uu ON @userId = uu.user_id
LEFT JOIN friend_request fr ON @userId = fr.user_id
WHERE ug.user_id = u.id AND u.profile_id = p.id
AND (ub.blocker_id IS NULL OR ub.blocker_id != ug.user_id)
AND (ub2.blocked_id IS NULL OR ub2.blocked_id != ug.user_id)
AND (uu.user_friends_id IS NULL OR uu.user_friends_id != ug.user_id)
AND (fr.to_user_id IS NULL OR (fr.to_user_id != ug.user_id))
AND ug.user_id!=@userId
AND p.suggestible
AND goal_id IN (SELECT iug.goal_id FROM user_goal iug WHERE user_id=@userId)
GROUP BY user_id ORDER BY matches DESC LIMIT 4
You can improve this overall query by removing that @userId
to start with ;) You can just reference previously mentioned columns to maintain that join.
This may then pose a problem with your subquery; which does not need to be there. That subquery will almost certainly be causing slow query execution here. You can incorporate the subquery into a derived table that you're also joining against (since it's using the same user_id
), this way it only executes once, rather than once for each record in the resultset as is happening now.
SELECT
ug.user_id,
COUNT(DISTINCT goal_id) as matches
FROM
user_goal ug
INNER JOIN user u ON ug.user_id = u.id
INNER JOIN profile p ON u.profile_id = p.id
LEFT JOIN user_block ub ON u.id = ub.blocked_id
LEFT JOIN user_block ub2 ON u.id = ub2.blocker_id
LEFT JOIN user_user uu ON u.id = uu.user_id
LEFT JOIN friend_request fr ON u.id = fr.user_id
LEFT JOIN (
SELECT iug.goal_id FROM user_goal
) iug ON iug.user_id = u.id
WHERE
/* I looked at your WHERE clause for a while and couldn't get my head
around what you're trying to do so I'll leave this to you */
Putting the sub-query directly into a JOIN and naming it creates a derived table, really speeding things up (and tidying things up IMHO).
精彩评论