MYSQL查询执行时间 - MYSQL query execution time

我有一个问题要问你。我有这个数据库与250.000录音，有2个文本字段，每个包含多达300字。我想选择所有符合条件的数据放到另一个表中。我想删除那些不符合我条件的记录，用这个命令:

DELETE FROM `cables` WHERE 
`data` NOT LIKE "%BRV%" AND
`data` NOT LIKE "%Venezuela%" AND
`data` NOT LIKE "%Caracas%" AND
`data` NOT LIKE "%Hugo Chavez%" AND
`tags` NOT LIKE "%BRV%" AND
`tags` NOT LIKE "%Venezuela%" AND
`tags` NOT LIKE "%Caracas%" AND
`tags` NOT LIKE "%Hugo Chavez%" AND
`header` NOT LIKE "%BRV%" AND
`header` NOT LIKE "%Venezuela%" AND
`header` NOT LIKE "%Caracas%" AND
`header` NOT LIKE "%Hugo Chavez%" AND
`subject` NOT LIKE "%BRV%" AND
`subject` NOT LIKE "%Venezuela%" AND
`subject` NOT LIKE "%Caracas%" AND
`subject` NOT LIKE "%Hugo Chavez%" AND
`tmp` NOT LIKE "%BRV%" AND
`tmp` NOT LIKE "%Venezuela%" AND
`tmp` NOT LIKE "%Caracas%" AND
`tmp` NOT LIKE "%Hugo Chavez%" AND
`identifier` NOT LIKE "%BRV%" AND
`identifier` NOT LIKE "%Venezuela%" AND
`identifier` NOT LIKE "%Caracas%" AND
`identifier` NOT LIKE "%Hugo Chavez%"

如果每行至少一次包含这些单词中的任何一个，则OK。问题是，从它开始执行到现在，我已经有3个小时了，但什么也没发生。我已经停止了这个过程，但什么也没发生。最终的结果应该有大约14000个录音，我该怎么办?谢谢你! !

什么都没发生，因为你阻止了它。因此，commit;还没有完成。

你应该拆分你的查询以缩短执行时间。

NOT LIKE %%非常非常昂贵!!

尝试REGEXP，可能在这里表现更好。

DELETE FROM `cables` WHERE
  `data` NOT REGEXP 'BRV|Venezuela|Caracas|Hugo Chavez' AND
  `tags` NOT REGEXP 'BRV|Venezuela|Caracas|Hugo Chavez' AND
  `header` NOT REGEXP 'BRV|Venezuela|Caracas|Hugo Chavez' AND
  `subject` NOT REGEXP 'BRV|Venezuela|Caracas|Hugo Chavez' AND
  `tmp` NOT REGEXP 'BRV|Venezuela|Caracas|Hugo Chavez' AND
  `identifier` NOT REGEXP 'BRV|Venezuela|Caracas|Hugo Chavez'

也可以试试串联(Karolis的建议)

DELETE FROM `cables` WHERE
   CONCAT( `data`, `tags`, `header`, `subject`, `tmp`, `identifier` )
   NOT REGEXP 'BRV|Venezuela|Caracas|Hugo Chavez'

问题是LIKE %text%将不使用全文索引。因此，一个有250,000个条目和大量LIKE %%标准的大表将花费很长时间。

你确定你需要开头的"%…"吗?否则，您可以尝试使用布尔搜索修饰符。

为什么不反过来选择匹配的记录而不是删除不匹配的记录呢?然后把它们插入到另一个表中?这可能会更快，因为3小时，即使过度使用不像条件，对于250,000行来说也太长了。

INSER INTO `selected_cables`
SELECT * FROM `cables`
WHERE NOT ( 
`data` NOT LIKE "%BRV%" AND
`data` NOT LIKE "%Venezuela%" AND
`data` NOT LIKE "%Caracas%" AND
`data` NOT LIKE "%Hugo Chavez%" AND
`tags` NOT LIKE "%BRV%" AND
`tags` NOT LIKE "%Venezuela%" AND
`tags` NOT LIKE "%Caracas%" AND
`tags` NOT LIKE "%Hugo Chavez%" AND
`header` NOT LIKE "%BRV%" AND
`header` NOT LIKE "%Venezuela%" AND
`header` NOT LIKE "%Caracas%" AND
`header` NOT LIKE "%Hugo Chavez%" AND
`subject` NOT LIKE "%BRV%" AND
`subject` NOT LIKE "%Venezuela%" AND
`subject` NOT LIKE "%Caracas%" AND
`subject` NOT LIKE "%Hugo Chavez%" AND
`tmp` NOT LIKE "%BRV%" AND
`tmp` NOT LIKE "%Venezuela%" AND
`tmp` NOT LIKE "%Caracas%" AND
`tmp` NOT LIKE "%Hugo Chavez%" AND
`identifier` NOT LIKE "%BRV%" AND
`identifier` NOT LIKE "%Venezuela%" AND
`identifier` NOT LIKE "%Caracas%" AND
`identifier` NOT LIKE "%Hugo Chavez%")