You can actually index functions or expression: https://www.sqlite.org/expridx.html
On Thu, Feb 16, 2017 at 9:32 PM, Cecil Westerhof <cldwester...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
