本文介绍了MYSQL嵌套查询运行很慢?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
下面的查询不断超时，有没有更少开销的方式来实现同样的功能?
The following query is constantly timing out, is there a less overhead way to achieve the same function ?
UPDATE Invoices SET ispaid = 0
WHERE Invoice_number IN (SELECT invoice_number
FROM payment_allocation
WHERE transactionID=305)
我正在做的是从交易中取消分配发票，最多可以返回 30 多条记录，但每次我尝试运行它都会停止数据库死
What I'm doing is unallocating invoices from a transaction, there can be up to 30+ records returned but it stops the database dead everytime I try to run it
推荐答案
使用 JOIN 而不是 subquery 会提高性能.
USE JOIN instead of subquery it will improve the performance.
如果您尚未创建，请在两个表中的 Invoice_number 列上创建索引.
Create index on Invoice_number column in both table if you haven't created.
试试这个:
UPDATE Invoices i
INNER JOIN payment_allocation pa ON i.Invoice_number = pa.invoice_number
SET i.ispaid = 0
WHERE pa.transactionID = 305;
这篇关于MYSQL嵌套查询运行很慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！