我有两张桌子TABLE_A
和TABLE_B
。如果TABLE_A
名称不存在,我想更新TABLE_B
状态字段。当我执行下面的查询时,它花费了超过75分钟。我对Table_B
列b_id
、m_id
和status
建立了索引。我不确定如何在秒内执行此操作。我在更新索引列的时候做错了什么吗?Table_B
的统计数据显示在附图中。
UPDATE TABLE_B
SET status = 3
WHERE ( status = 1
OR status IS NULL )
AND b_id = 39
AND m_id = 2
AND NAME NOT IN (SELECT NAME
FROM TABLE_A
WHERE b_id = 39
AND m_id = 2)
发布于 2019-05-22 03:17:01
您可以尝试使用left join where不匹配而不是NOT IN
但是要确保你有合适的综合索引
TABLE_A columns ( NAME,b_id, m_id)
TABLE_B columns (NAME, b_id, m_id, status)
UPDATE TABLE_B
LEFT JOIN TABLE_A ON TABLE_B.NAME = TABLE_A.NAME AND TABLE_A.b_id = 39 and TABLE_A.m_id
SET status = 3
WHERE ( status = 1 OR status IS NULL )
AND TABLE_B.b_id = 39
AND TABLE_B.m_id = 2
AND TABLE_A.NAME IS NULL
https://stackoverflow.com/questions/56244859
复制相似问题