Can the optimizer recognize these "instant" functions and perhaps lean towards using them?
Are there cases where this would NOT be good? I did show you can construct a query which runs at the "correct" speed. sqlite> select * from (select min(i) from test) as a,(select max(i) from test) as b, (select count(i) from test) as c; min( max(i) coun ---- ------------- ---- 37 2147483025 10000000 CPU Time: user 1.807726 sys 0.079987 sqlite> select min(i),max(i),count(i) from test; 37|2147483025|10000000 CPU Time: user 4.431326 sys 0.128981 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Saturday, June 25, 2011 9:53 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] question about sqlite aggregate funcitons On 25 Jun 2011, at 1:37pm, Luuk wrote: > Giving the SELECTs i a different order does not improve thing (much). > BTW, i'm doing this on a Windows 7 machine, and will test what happens > on a Linux machine too, to see if there's difference. > > SQLite version 3.7.2 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .timer on > sqlite> select min(i) from test; > 1 > CPU Time: user 0.000000 sys 0.000000 > sqlite> select max(i) from test; > 9999999 > CPU Time: user 0.000000 sys 0.000000 > sqlite> select count(i) from test; > 9999999 > CPU Time: user 1.560010 sys 0.296402 > sqlite> select min(i),max(i),count(i) from test; > 1|9999999|9999999 > CPU Time: user 4.430428 sys 0.312002 Luuk, because of the structure SQLite uses for indexes, it can find both max() and min() instantly, but has to actually read every record to find count(). I know this seems weird, but that's the way it is. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
