Aliases in SQL are not the same as variables in most procedural languages. So every time you mention 'totaltime' SQLite is probably recalculating that value by adding all the columns together. See the various discussions regarding no deterministic (random) functions last year. Less references to that alias => less calculations. Reference it twice and expect it to be slower.
> On 17 Feb 2017, at 12:27 AM, Darko Volaric <li...@darko.org> wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
