MYSQL查询执行时间
我有一个问题要问你。我的数据库包含 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%"
如果每一行至少包含一次这些单词，那么它就可以。问题是，自从它执行以来我已经有 3 个小时了，但什么也没发生。我已经停止了该过程，但什么也没有发生。最终结果应该有大约 14000 个录音，我能做什么？谢谢你！！！！
I have a question for you. I have this database with 250.000 recordings, with 2 text fields each containing up to 300 words. And I want do select all the data that meets some criteria and put it in another table. I thought to delete those recording that are not satisfying my condition with this wuery:
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%"
Each row is OK if it containt at least one time any of those words. The thing is that I already have 3 hours since it is being in execution, and nothing hapened. I've stopped the proccess and nothing happened. The final resuls should have somewhere around 14000 recordings, What can I do? Thank you!!!!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
什么都没发生，因为你阻止了它。因此，
commit;尚未完成。您应该拆分查询以缩短执行时间。
NOT LIKE %%非常非常昂贵！ （索引，如果有一些没用到……）Nothing happened because you stopped it. So, the
commit;has not been done.You should split your query to have a shorter execution.
The
NOT LIKE %%are very very expensive!!! (the index, if there are some are not used...)尝试
REGEXP，可能在这里表现更好。也尝试连接（Karolis 的建议）
try
REGEXP, might perform better here.also try it with concatenation (Karolis' suggestion)
问题是
LIKE %text%将不会使用全文索引。因此，一个包含 250.000 个条目和大量LIKE %%标准的大表将花费非常长的时间。您确定需要前导“%...”吗？否则，您可以尝试使用布尔搜索修饰符。
The problem is that
LIKE %text%will not make use of a fulltext index. So a big table with 250.000 entries and a lot ofLIKE %%criterias will take an awful long time.Are you sure you need the leading '%...'? Otherwise you can try to use Boolean search modifier.
为什么不反转条件以选择匹配的记录，而不是删除不匹配的记录？然后将它们插入到另一个表中？这可能会更快，因为即使过度使用不相似的条件，3 小时对于 250'000 行来说也太长了。
Why not reverse your condition to select the records that match instead of deleting the ones that don't? And then insert those into the other table? This might very well be faster, as 3 hours, even for the overuse of not-like conditions, is way too long for 250'000 rows.