MySQL优化子查询
我想优化这个查询（因为子查询一般来说速度不快），但是我迷失了，因为我无法使用联接重写它，这对性能会更好，你能帮助我解决这个问题吗？
SELECT id, company, street, number, number_addition, postalcode, telephone
FROM clients
WHERE (postalcode BETWEEN '1000' AND '9000') AND street = (
SELECT DISTINCT street FROM clients WHERE (postalcode BETWEEN '1000' AND '9000') AND postalcode <= (
SELECT MIN(postalcode) FROM clients WHERE street = 'Main Street' AND (postalcode BETWEEN '1000' AND '9000'))
ORDER BY postalcode DESC LIMIT 1, 1)
ORDER BY postalcode DESC, street DESC, number DESC, number_addition DESC, telephone DESC
LIMIT 1
谢谢你们的宝贵时间。
I want to optimize this query (since subqueries aren't fast in general), but I'm lost because I cannot rewrite this using joins which will be better for performance, can you help mi with this?
SELECT id, company, street, number, number_addition, postalcode, telephone
FROM clients
WHERE (postalcode BETWEEN '1000' AND '9000') AND street = (
SELECT DISTINCT street FROM clients WHERE (postalcode BETWEEN '1000' AND '9000') AND postalcode <= (
SELECT MIN(postalcode) FROM clients WHERE street = 'Main Street' AND (postalcode BETWEEN '1000' AND '9000'))
ORDER BY postalcode DESC LIMIT 1, 1)
ORDER BY postalcode DESC, street DESC, number DESC, number_addition DESC, telephone DESC
LIMIT 1
Thanks for your time guys.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SELECT DISTINCT street ORDER BY postalcode没有意义（我认为这不是有效的 ANSI SQL），除非postalcode在功能上依赖于street——我认为不是，因为如果是的话，你的 get-lowest-postalcode-on-Main-Street 内部子选择就没有意义。 MySQL 会让你侥幸逃脱，但结果会不一致。你想在这里说什么？我认为这应该不会特别慢，因为您所拥有的不是依赖子查询；子查询仅执行一次，并且不会对每个外部行重复执行。您可以将其重写为三个单独的查询 -
执行上没有区别。 （事实上，为了清楚起见，这样做可能更好。）
您可以将它们重写为连接，使用 self-left-joins-on-less-than-is-null 来获取最小值/最大值，但我认为对于这个例子你不会获得任何好处，并且考虑到两个级别的加入和第二高的要求，它会变得非常混乱。这个查询在实践中是否特别慢？
EXPLAIN是什么样的？您是否已将邮政编码和街道编入索引？SELECT DISTINCT street ORDER BY postalcodedoesn't make sense (and I think isn't valid ANSI SQL), unlesspostalcodeis functionally-dependent onstreet—which I don't think it is, as your get-lowest-postalcode-on-Main-Street inner subselect wouldn't make sense if it was. MySQL will let you get away with it but the results will be inconsistent. What are you trying to say here?I don't think this should be particularly slow since what you have isn't a dependent subquery; the subqueries are executed only once and not repeatedly for each outer row. You could rewrite it as three separate queries—
with no difference in execution. (Indeed, it might be better to do so for clarity.)
You could rewrite these as joins, using self-left-joins-on-less-than-is-null to get the minima/maxima, but I don't think you'd gain anything by it for this example and it would get very messy given the two levels of joining and the second-highest requirement. Is this query particularly slow in practice? What does the
EXPLAINlook like? Have you indexedpostalcodeandstreet?