Cecil Westerhof <cldwester...@gmail.com> wrote: > I have a table vmstat that I use to store vmstat info. ;-) > At the moment it has more as 661 thousand records. > > In principle the values of usertime, systemtime, idletime, waittime and > stolentime should add up to 100. I just wanted to check it. Of-course there > could be a rounding error, so I wrote the following query: > SELECT date > , time > , usertime > , systemtime > , idletime > , waittime > , stolentime > , (usertime + systemtime + idletime + waittime + stolentime) AS > totaltime > FROM vmstat > WHERE totaltime < 99 OR totaltime > 101 > > I did not like that, so I rewrote the WHERE to: > WHERE ABS(100 - totaltime) > 1 > > The funny thing the second WHERE is more efficient as the first, where I > would have expected it to be the other way around. > The first takes around 1.050 milliseconds. > The second takes around 950 milliseconds. > So the second is around 10% more efficient. Why is this? > > In case it is important: I did this in sqlitebrowser 3.7.0, which uses > SQLite 3.8.10.2.
I suspect that when you use "WHERE ABS(100 - totaltime) > 1" SQLite cannot use an index since it's an expression, so it does a full table scan, whereas the other solution which does "WHERE totaltime < 99 OR totaltime > 101" may use an index on totaltime (assuming that there is an index). In general using an index is good. But if most of the records satisfy the condition "ABS(100 - totaltime) > 1" then an index can be more harmful than useful. And that could explain why using "WHERE ABS(100 - totaltime) > 1" is a bit faster. You could try "EXPLAIN QUERY PLAN" on your queries to see if they use an index or if they do a full table scan. Dominique _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
