查询优化--“where not in”on self

Question

我正在尝试开发一个查询，它将删除数据库中除最近添加的行之外的所有行。这基于存储为字符串的时间戳字段和存储为字符串的用户ID字段。

table.Timestamp -> text field
table.Retrieving_User -> text field

这是我开发的查询。我们在这个数据库中有大约50K条记录，它运行得非常慢。我希望这不是因为我正在做的字符串到日期的转换，因为这是需要完成的。

DELETE 
FROM `table` main
WHERE (main.Retrieving_User, STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) NOT IN 
    (SELECT  sub.Retrieving_User, MAX( STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) 
    FROM `table` sub
    WHERE sub.Retrieving_User = 'userID'
    GROUP BY sub.Retrieving_User )
AND main.Retrieving_User = 'userID'

有没有人知道一种更有效的方法来做我正在尝试做的事情？

Answer 1

这样的代码可能运行得更快，因为它不使用IN语句，该语句可能会在内存中的表上反复循环。备份并重试

DELETE 
FROM `table` main
WHERE STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' )<
  (SELECT  MAX( STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' ) 
   FROM `table` sub
   WHERE sub.Retrieving_User = main.Retrieving_User )
AND main.Retrieving_User = 'userID'

Answer 2

当您要删除许多行，并且保留的行数比要删除的行数少得多时，MySQL documentation中的这个技巧效果非常好：

如果要从大型表中删除许多行，则可能会超出InnoDB表的锁表大小。为了避免这个问题，或者只是为了最小化表保持锁定的时间，以下策略(根本不使用DELETE )可能会有所帮助：

将不需要删除的行选择到与原始表结构相同的空表中: INSERT INTO t_copy SELECT * FROM t WHERE ...；使用RENAME table自动移动原始表，并将副本重命名为原始名称: RENAME TABLE t to t_old，t_copy to t；Drop原始表: DROP TABLE t_old；

使用MyISAM缩短删除时间的另一种方法是使用快速删除，然后再从MySQL文档中优化表：

如果要从表中删除许多行，使用delete QUICK，然后使用OPTIMIZE table可能会更快。这将重建索引，而不是执行许多索引块合并操作。

这是对IvoTops answer进行了优化。我们只需将日期转换回字符串，这样就不必在外部查询中再次执行转换：

DELETE 
FROM `table` main
WHERE main.Timestamp <>
  (SELECT DATE_FORMAT(MAX(STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y'), '%a %b %d %H:%i:%s CST %Y') 
   FROM `table` sub
   WHERE sub.Retrieving_User = main.Retrieving_User )
AND main.Retrieving_User = 'userID'

Answer 3

我认为您的性能问题与NOT IN语句有关。你可能会过得更好

DELETE `table`
FROM `table` main,
     (SELECT  sub.Retrieving_User, MAX( STR_To_DATE( sub.Timestamp , '%a %b %d %H:%i:%s CST %Y' )) maxTime
    WHERE sub.Retrieving_User = 'userID'
    GROUP BY sub.Retrieving_User) sub
WHERE STR_To_DATE( main.Timestamp , '%a %b %d %H:%i:%s CST %Y' ) < sub.maxTime
  AND main.Retrieving_User = 'userID';