我想知道为什么我在mysql 5.5上删除myisam表中的删除太慢了(我必须取消(
此SQL少于一秒钟:
SELECT id FROM backup
WHERE id > 0
AND userId NOT IN
(SELECT id FROM user WHERE state > 0)
ORDER BY id ASC LIMIT 100;
此相同的删除未完成2分钟(我取消(
DELETE FROM backup
WHERE id > 0
AND userId NOT IN
(SELECT id FROM user WHERE state > 0)
ORDER BY id ASC LIMIT 100;
但是以下删除 - 查询相同的行(刚才由其主键选择( - 花了一秒钟的时间完成!
DELETE FROM backup WHERE id IN (12, 33, 34, [... 100 ids...])
编辑我还尝试使用子声音进行了此修改 - 一分钟后,我就会取消它...
DELETE FROM backup WHERE id IN (
SELECT ID FROM (
SELECT id FROM backup WHERE id > 0
AND userId NOT IN (SELECT id FROM user WHERE state > 0)
ORDER BY id ASC LIMIT 100
) a
);
使用内部连接不适用于删除,因为我使用订单。
有什么想法?
好吧,问题实际上是子查询 - 分别是订单和限制语句。
当我省略订单并限制并使用以下查询时,它的速度是预期的(不到第二个(:
:DELETE backup FROM backup
INNER JOIN user ON user.id = backup.userId
WHERE backup.id > 0 AND backup.id < 276 AND (state = 0 OR state IS NULL);