英文:
Does using WHERE col_name LIKE statement increase performance?
问题
I have always thought a WHERE statement was good for performance and other answers here tend agree, but looking at the following I am wondering if the WHERE statement helps.
UPDATE prod_table SET desc = REPLACE(desc, 'old text', 'new text') WHERE desc LIKE '%old text%'
基本上，在大约30000个项目中更改产品描述的部分内容，大约有300个项目需要更改。由于现有描述仅存在于需要更改的位置，从逻辑上讲，WHERE语句是不必要的。我对SQL的内部工作方式一无所知，所以不确定LIKE搜索是否更快地识别需要更新的记录，或者运行REPLACE是否会提供类似的性能。
在这种情况下，这并不重要 - 我真的正在寻求一些关于逻辑上不必要的WHERE语句是否实际上对性能或其他良好实践理由有用的澄清。
英文:
I have always thought a WHERE statement was good for performance and other answers here tend agree, but looking at the following I am wondering if the WHERE statement helps.
UPDATE prod_table SET desc = REPLACE(desc, 'old text', 'new text') WHERE desc LIKE '%old text%'
Basically changing a portion of a product description in around 300 items out of around 30000. Since the existing description only exists where it needs to be changed, logically the WHERE statement is unnecessary. I have no idea of the inner workings of SQL so unsure if the LIKE search would more quickly identify the records that require updating or if running REPLACE on everything would yield similar performance.
In this instance it is not important - I am really seeking some clarification on the question if a logically unnecessary WHERE statement is actually useful for performance or other good practice reasons.
答案1
得分: 0
使用WHERE子句将更快，因为更新操作的数量较少，差异不会很大，但会因数据库而异。
两种方式都会从磁盘读取每一行并执行类似的操作，要么查找和替换文本，要么只查找 - 那里没有太大的区别。
根据数据库的不同，无操作更新的成本会有所不同，这将是性能差异的原因。
英文:
With the WHERE clause will be faster because the number of update operations is less, and the difference shouldn't be that great, but it will vary from database to database.
Both ways read every row from disk and perform a similar operation, either finding and replacing text, or just finding - there's not much difference there.
Depending on the database, the cost of a no-op update varies and that will be the source of the difference in performance.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论