更新查询缓慢

Question

从select语句更新表时遇到问题...下面是命令：

UPDATE CrashReport SET STATUS = '2' WHERE id IN (
    SELECT report_id 
    FROM CrashInfo 
    WHERE info_title LIKE 'STACK_TRACE_ID'
    AND info_value IN (
        SELECT info_value
        FROM CrashInfo
        WHERE report_id = **report_id**
        AND info_title = 'STACK_TRACE_ID'
    )
)

我要传递给此查询的参数将是sub sub查询中的report_id...select单独执行需要0.113秒，而update查询总共需要4.868秒。是因为update查询将对表的每一行执行select语句吗？我怎么能让这更快？

谢谢你

Answer 1

类似这样的事情：

SET @myVal = (
    SELECT info_value
    FROM CrashInfo
    WHERE report_id = **report_id**
    AND info_title = 'STACK_TRACE_ID');

UPDATE CrashReport SET STATUS = '2' WHERE id IN (
SELECT report_id 
FROM CrashInfo 
WHERE info_title LIKE 'STACK_TRACE_ID'
AND info_value IN (@myVal)
)