MySql删除优化

Question

这是我试图执行的查询

DELETE FROM testmachine WHERE workdone != 0 AND timetaken < 1617215400 LIMIT 1000;

执行查询需要50秒以上的时间。我想提高速度。

有没有办法减少执行时间？

这是我的专栏模式：

id  int UN AI PK
att text
ways    varchar(100)
mediuminv   text
mediumid    varchar(45)
timetaken   int
workdone    int
in_use  tinyint UN
status  tinyint
in  text
out text

这是索引：

Index: idx_time_taken_work_done_in_use_status
Definition:
Type    BTREE
Unique  No
Visible Yes
Columns done
time
in_use
status

TIA-

Answer 1

如果查询用于以下位置：

REPEAT
DELETE FROM testmachine WHERE workdone != 0 AND timetaken < 1617215400 LIMIT 1000;
UNTIL !ROW_COUNT() END REPEAT;

然后尝试

ALTER TABLE testmachine 
ADD COLUMN flag BOOLEAN AS (workdone != 0 AND timetaken < 1617215400) VIRTUAL,
ADD INDEX idx_flag (flag);
REPEAT
DELETE FROM testmachine WHERE flag LIMIT 1000;
UNTIL !ROW_COUNT() END REPEAT;
ALTER TABLE testmachine
DROP INDEX idx_flag,
DROP COLUMN flag;