> But SQLite knows not to UPDATE a row when your update changes no values.
This is not possible and would make an update operation incredibly slow. What you mean is that a row which is not updated is not updated. A row which is updated, even if you are re-writing the same value, *IS* an update operation. The database engine has no way of knowing that you updated the value with the same value(s) it already contained without doing very expensive comparison operations of each field. sqlite> create virtual table n using wholenumber; sqlite> create table x(x integer); sqlite> insert into x select random() from n where value between 1 and 100000; sqlite> .eqp on sqlite> .timer on sqlite> update x set x=x where cast(x/2 as integer)*2=x; --EQP-- 0,0,0,SCAN TABLE x Run Time: real 0.087 user 0.078125 sys 0.000000 sqlite> update x set x=x; --EQP-- 0,0,0,SCAN TABLE x Run Time: real 0.110 user 0.109375 sys 0.000000 Or to make the difference more obvious: sqlite> insert into x select random() from n where value between 1 and 1000000; --EQP-- 0,0,0,SCAN TABLE n VIRTUAL TABLE INDEX 10: Run Time: real 0.686 user 0.640625 sys 0.031250 sqlite> update x set x=x where cast(x/2 as integer)*2=x; --EQP-- 0,0,0,SCAN TABLE x Run Time: real 0.995 user 0.984375 sys 0.000000 sqlite> update x set x=x; --EQP-- 0,0,0,SCAN TABLE x Run Time: real 1.324 user 1.312500 sys 0.000000 sqlite>
