查询优化 - 'where not in' 自我 [英] query optimization - 'where not in' on self

问题描述

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

I am trying to develop a query that will delete all but the most recently added row in a database. This is based on a Timestamp field, that is stored as a string and a User ID field that is stored as a string..

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

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

This is the query I have developed. We have around 50K records in this database and it runs very slowly. I hope its not because of the string to date conversion that I'm doing, because this needs to be done.

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'

有没有人知道一种更有效的方式来做我想做的事情?

Does anyone know of a more efficient way of doing what I'm trying to do?

推荐答案

这样的事情可能会运行得更快，因为它不使用可能在内存表上一次又一次循环的 IN 语句.备份并尝试

Something like this might work faster because it does not use the IN statement that might be looping again and again over an in memory table. Backup and try

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'

这篇关于查询优化 - 'where not in' 自我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！