MYSQL嵌套查询运行很慢?

2022-01-23 00:00:00 join sql-update subquery 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;

相关文章