MySQL 子查询优化
查询：
SELECT a FROM table1 WHERE b IN (SELECT b FROM table1 WHERE c = 2);
如果子查询返回零结果，mysql需要很长时间才能意识到它最终返回空结果。 （子查询为空时2.5s，有子查询结果时0.0005s）。
我的问题：有没有办法修改查询，使其仍然返回空结果，但花费的时间与有结果时相同？
我尝试过：
SELECT a FROM table1 WHERE b IN ((SELECT b FROM table1 WHERE c = 2), 555);
...但它仅在子查询为空时才有效。如果有结果，则查询失败。
-- 我不想将查询格式从嵌套更改为联接/等。
想法..？谢谢！
-- 编辑：另外，我忘记添加：子查询可能会产生一个相当大小的结果列表，而不仅仅是一个结果。 --- 另外，当我输入“555”时，我指的是该列中永远不会存在的值。
-- 编辑2：我还尝试了以下查询，它“有效”，但当它有结果时，它仍然比原始查询长几个数量级：
SELECT a FROM table1 WHERE b IN (SELECT 555 AS b UNION SELECT b FROM table1 WHERE c = 2);
The Query:
SELECT a FROM table1 WHERE b IN (SELECT b FROM table1 WHERE c = 2);
If the subquery returns zero results, mysql takes a long time to realize it before it finally returns an empty result. (2.5s when subquery is empty, 0.0005s when there is a subquery result).
My question: is there a way to modify the query such that it will still return an empty result but take the same time as it did when there was a result?
I tried:
SELECT a FROM table1 WHERE b IN ((SELECT b FROM table1 WHERE c = 2), 555);
...but it only works WHEN the subquery is empty. If there is a result, the query fails.
-- I don't want to change the query format from nested to join/etc.
Ideas..? Thanks!
-- EDIT: Also, I forgot to add: The subquery will likely result in a decent-sized list of results, not just one result. --- ALSO, when I type '555', I am referring to a value that will never exist in the column.
-- EDIT 2: I also tried the following query and it "works" but it still takes several orders of magnitude longer than the original query when it has results:
SELECT a FROM table1 WHERE b IN (SELECT 555 AS b UNION SELECT b FROM table1 WHERE c = 2);
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
疯狂猜测（我现在无法测试）：
Wild guess (I can't test it right now):