我在PHP中遇到这个查询的问题。它太慢了,我在执行后几分钟内无法连接到服务器。我一直在从主查询中删除单独的子查询,发现在删除最后一个内部查询后,它运行得非常顺利。
表"u"有30.000行,表"u_r"约为17.000,表"uS"约为13.000,表"s"约为100。尽管表"u_r"answers"u_s"有很多行,但只有2-3行具有与条件匹配的相同"id_u"。
我希望,我提供了足够的信息。如果你还需要了解其他信息,请随时在评论中询问。
SELECT DISTINCT id_p
FROM u
WHERE
'$x' IN(
SELECT id_p
FROM u_p
WHERE id_u=u.id_u
)
AND
(
'$y' IN (
SELECT id_r
FROM u_r
WHERE id_u=u.id_u
)
OR
'$y' IN (
SELECT DISTINCT id_r
FROM s
WHERE id_s IN (
SELECT id_s //without this query, everything works fine
FROM u_s
WHERE id_u=u.id_u
)
)
)
我认为您可以更改:
SELECT DISTINCT id_r
FROM s
WHERE id_s IN (
SELECT id_s //without this query, everything works fine
FROM u_s
WHERE id_u=u.id_u
至
SELECT s.id_s
FROM u_s
INNER JOIN s ON u_s=id_u=u.id_u
WHERE u_s.id_u = u.id_u
GROUP BY s.id_r
没有测试,因为我正试图把我的头裹在结构上。
还记得检查索引。作为一个粗略的指南,您应该在"WHERE"answers"ON"中索引任何内容:
u_p.id_u
u_r.id_u
u_s.id_u
s.id_s
测试这是否会产生相同的结果-应该会快得多。
- 用内部联接替换IN语句。查询优化器有更多机会以这种方式进行优化
- 消除您的OR语句-我使用UNION来完成此操作。我认为,如果您愿意的话,您可能可以使用LEFT JOIN来消除OR
- 你需要测试一下——我没有任何样本数据
select distinct id_p from u inner join u_p on u.id_u = u_p.id_u and u_p.id_u = '$x' inner join ( select id_r.id_u from u_r where id_r = '$y' union select id_r from s inner join u_s on s.id_u = u_s.id_u where id_r = '$y' ) as subq on u.id_u = subq.id_u