2017-02-16 21:10 GMT+01:00 Dominique Pellé <dominique.pe...@gmail.com>:
> 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. > Totaltime is calculated, so it cannot have an index. ;-) Besides from the almost 700.000 records only two satisfy the condition. I should look into EXPLAIN QUERY PLAN. -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
