On 24 May 2015, at 4:16pm, Gert Van Assche <gertva at gmail.com> wrote:
> When I do an update in a huge table, should it write it like this > UPDATE T1 SET F1=replace(F1, 'x', 'y') WHERE F1 LIKE '%x%'; > or without where clause like this > UPDATE T1 SET F1=replace(F1, 'x', 'y'); > > T1 has an index on F1, which is a TEXT field. Neat question. Theoretically, the answer would depend on the proportion of the rows which should be changed. So you could not get an answer which is fastest no matter what you are searching for. But SQLite knows not to UPDATE a row when your update changes no values. So in the real world I would expect SQLite to perform best without the WHERE clause. This would also involve using less memory since there would be no need to prepare the list of rows which the clause has selected. Also it keeps your code simpler, which should involve fewer bugs. However it will require more use of the REPLACE() function, and if this function is slow that would mean the whole process can take longer. I cannot guess a 100% correct answer to your question. Simon.
