MYSQL嵌套查询运行得很慢? [英] MYSQL nested query running very slow?

问题描述

以下查询一直在超时，是否有较少的开销来实现相同的功能?

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 代替子查询，它将提高性能.

USE JOIN instead of subquery it will improve the performance.

创建索引.

尝试一下:

UPDATE Invoices i 
INNER JOIN payment_allocation pa ON i.Invoice_number = pa.invoice_number 
SET i.ispaid = 0 
WHERE pa.transactionID = 305;

这篇关于MYSQL嵌套查询运行得很慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！