我一直在尝试将包含多个联合的查询更改为包含多个左连接的查询。
联合查询为
SELECT DISTINCT user_id,typ
FROM (SELECT user_id,
'review' AS typ
FROM review
WHERE time > '2011-12-01'
UNION
SELECT user_id,
'invite' AS typ
FROM invite
WHERE time_created > '2011-12-01'
OR time_action > '2011-12-01'
UNION
SELECT user_id,
'voter' AS typ
FROM vote
WHERE time > '2011-12-01'
UNION
SELECT user_id,
'voted' AS typ
FROM review
WHERE review_id IN (SELECT object_id
FROM vote
WHERE vote = 1
AND type = 'review'
AND time > '2011-12-01')
UNION
SELECT user_id,
'comment' AS typ
FROM comment
WHERE time > '2011-12-01'
UNION
SELECT user_id,
'joined' AS typ
FROM USER
WHERE updated_time > '2011-12-01'
OR last_login_date > '2011-12-01'
UNION
SELECT user_id,
'wishlist' AS typ
FROM user_wishlist
WHERE time > '2011-12-01'
UNION
SELECT introducer,
'introducer' AS typ
FROM introduction
WHERE time > '2011-12-01'
UNION
SELECT user_id,
'answer' AS typ
FROM answer
WHERE time > '2011-12-01') T
WHERE T.user_id IN (SELECT user_id
FROM USER
WHERE user_id < 5245762)
我可以把它改成这样:
SELECT t1.user_id,
IF(t2.user_id, 'yes', 'no') AS review,
IF(t3.user_id, 'yes', 'no') AS invite,
IF(t4.user_id, 'yes', 'no') AS voter,
IF(t5.user_id, 'yes', 'no') AS voted,
IF(t6.user_id, 'yes', 'no') AS comment,
IF(t7.user_id, 'yes', 'no') AS joined,
IF(t8.user_id, 'yes', 'no') AS wishlist,
IF(t9.user_id, 'yes', 'no') AS introducer,
IF(t10.user_id, 'yes', 'no') AS answer
FROM (SELECT user_id
FROM user
WHERE user_id < 5245762) t1
LEFT JOIN (SELECT DISTINCT user_id
FROM review
WHERE time > '2011-12-01') t2
ON t1.user_id = t2.user_id
LEFT JOIN (SELECT DISTINCT user_id
FROM invite
WHERE time_created > '2011-12-01'
OR time_action > '2011-12-01') t3
ON t1.user_id = t3.user_id
LEFT JOIN (SELECT DISTINCT user_id
FROM vote
WHERE time > '2011-12-01') t4
ON t1.user_id = t4.user_id
LEFT JOIN (SELECT DISTINCT user_id
FROM review
WHERE review_id IN (SELECT object_id
FROM vote
WHERE vote = 1
AND type = 'review'
AND time > '2011-12-01')) t5
ON t1.user_id = t5.user_id
LEFT JOIN (SELECT DISTINCT user_id
FROM comment
WHERE time > '2011-12-01') t6
ON t1.user_id = t6.user_id
LEFT JOIN (SELECT DISTINCT user_id
FROM user
WHERE updated_time > '2011-12-01'
OR last_login_date > '2011-12-01') t7
ON t1.user_id = t7.user_id
LEFT JOIN (SELECT DISTINCT user_id
FROM user_wishlist
WHERE time > '2011-12-01') t8
ON t1.user_id = t8.user_id
LEFT JOIN (SELECT DISTINCT introducer AS user_id
FROM introduction
WHERE time > '2011-12-01') t9
ON t1.user_id = t9.user_id
LEFT JOIN (SELECT DISTINCT user_id
FROM answer
WHERE time > '2011-12-01') t10
ON t1.user_id = t10.user_id
现在我知道两个查询的结果会有点不同,第一个查询用于提供像
这样的结果user_id typ
1 invite
2 invite
1 voter
1 voted
2 voted
左连接查询提供如下结果
user_id invite voter voted
1 yes yes yes
2 yes no yes
现在,当user_id的列表大约小于500时(联合查询中的最后一个WHERE和左连接查询中的表t1),左连接查询更快,之后,联合查询更快。但是在运行左连接查询之后,我发现联合查询缺少某些用户的数据(没有user_id的行),而左连接查询提供了准确的结果。有没有办法让左连接查询比联合查询快?或者修复我的联合查询以准确地提供结果?
PS: The WHERE (T.user_id in…SELECT user_id from user…左连接查询条件下user_id是一个例子,它可以是多个表的自定义查询提供多行user_id列
你应该在mysql查询前使用"EXPLAIN"。它将显示查询的mysql执行计划以及可能的性能瓶颈。
参见mysql参考:https://dev.mysql.com/doc/refman/5.0/en/explain.html
Explain只能用于select查询,而不能用于update或delete。
我们不能给你更多的提示,因为你没有为你的表发布数据库模式定义,这些定义将显示你使用的字段类型和定义的键。