mysql - 慢更新查询

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'
    )
)

我传递给此查询的参数将是子子查询中的report_id...选择需要0.113秒来单独执行，但更新查询总共需要0.113秒执行时间为 4.868 秒。是因为更新查询将对表的每一行执行 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)
)