MySql SELECT里面的SELECT非常慢

Question

我在使用另一个 mysql select 时遇到问题。

此查询需要 0,0007 秒:

SELECT node.title AS node_title
FROM node node
WHERE (( (node.status = '1') AND (node.type IN  ('event')) AND (node.uid= 1) ))

但这需要 9 秒:

SELECT node.title AS node_title
FROM node node
WHERE (( (node.status = '1') AND (node.type IN  ('event')) AND (node.uid= 1)  OR node.nid IN (SELECT n.nid FROM tag n WHERE n.uid='1') ))

有人知道为什么吗？两者分开都非常快。

我要做的就是获取一些数据，我可以通过一些连接获取一些数据。我也尝试过这种方式，但遇到了同样的性能问题:

SELECT node.title AS node_title
FROM
node node
LEFT JOIN tag tag ON tag.uid = 4
WHERE (( (node.status = '1') AND (node.type IN  ('event')) AND (node.uid  = '4') OR (tag.nid = node.nid) ))

有什么想法吗？谢谢

Answer 1

有时或可能会以不好的方式影响查询计划。你可以试试这个:

SELECT n.title AS node_title
FROM node n
WHERE n.status = '1' AND n.type IN  ('event') AND n.uid = 1
union 
SELECT n.title
FROM node n
WHERE exists (SELECT t.nid FROM tag t WHERE t.uid = '1' and t.nid = n.nid);

为了使其发挥最佳效果，请具有以下两个索引:

node(status, type, uid)
tag(uid, nid)