And...this select does it at the speed you expect since it uses the indexes for
min/max.\
This one I ran with 3.7.7
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
Even though the query plan looks nastier:
sqlite> explain query plan select * from (select min(i) from test) as a,(select
max(i) from test) as b, (select count(i) from test) as c;
sele order from deta
---- ------------- ---- ----
1 0 0 SEARCH TABLE test USING COVERING INDEX idx (~1 rows)
2 0 0 SEARCH TABLE test USING COVERING INDEX idx (~1 rows)
3 0 0 SCAN TABLE test (~10000000 rows)
0 0 0 SCAN SUBQUERY 1 AS a (~1 rows)
0 1 1 SCAN SUBQUERY 2 AS b (~1 rows)
0 2 2 SCAN SUBQUERY 3 AS c (~1 rows)
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 Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Saturday, June 25, 2011 8:15 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] question about sqlite aggregate funcitons
OK...I generated 10M ints and imported them. I get times that I would expect.
select with all 3 is slightly less then the sum of the other 3 individually.
main()
{
int i;
for(i=0;i<10000000;i++) {
int j=rand();
printf("%d\n",j);
}
}
sqlite3 test.db
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(i int);
sqlite> .import x.csv test
sqlite> .timer on
sqlite> select min(i) from test;
37
CPU Time: user 1.993697 sys 0.097985
sqlite> select max(i) from test;
2147483025
CPU Time: user 1.972700 sys 0.123981
sqlite> select count(i) from test;
10000000
CPU Time: user 1.775730 sys 0.104984
sqlite> seledct min(i),max(i),count(i) from test;
CPU Time: user 0.000000 sys 0.000000
Error: near "seledct": syntax error
sqlite> select min(i),max(i),count(i) from test;
37|2147483025|10000000
CPU Time: user 4.431326 sys 0.128981
But if I put an index on "i" and then I get your behavior.
sqlite> create index idx on test(i);
CPU Time: user 41.206736 sys 60.683775
sqlite> .timer on
sqlite> select min(i) from test;
37
CPU Time: user 0.001000 sys 0.000000
sqlite> select max(i) from test;
2147483025
CPU Time: user 0.000000 sys 0.000000
sqlite> select count(i) from test;
10000000
CPU Time: user 1.448780 sys 0.388941
sqlite> select min(i),max(i),count(i) from test;
37|2147483025|10000000
CPU Time: user 4.038386 sys 0.439933
sqlite> explain query plan select count(i) from test;
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE test (~1000000 rows)
CPU Time: user 0.000000 sys 0.000000
sqlite> explain query plan select min(i),max(i),count(i) from test;
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE test (~1000000 rows)
CPU Time: user 0.001000 sys 0.000000
The difference is in your single select for min/max which can use the index.
sqlite> explain query plan select min(i) from test;
sele order from deta
---- ------------- ---- ----
0 0 0 SEARCH TABLE test USING COVERING INDEX idx (~1 rows)
Sqlite3 mistakenly thinks that just doing the table scan is faster than
index+index+tablescan which looks like it might win in this case. And
"analyze" doesn't change anything.
Don't know if the optimizer can recognize this case or if perhaps the optimizer
logic is wrong. Maybe it's just the min/max functions for example that benefit
a LOT from the index.
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 Luuk [luu...@gmail.com]
Sent: Saturday, June 25, 2011 7:37 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] question about sqlite aggregate funcitons
sorry, my reaction is at the bottom ;)
On 25-06-2011 13:59, Black, Michael (IS) wrote:
> Ummmm....caching?
>
>
>
> Try doing your first select last and see what happens. I'm betting your
> first "select min(*) from test" will be a lot slower.
>
>
>
> 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 Luuk [luu...@gmail.com]
> Sent: Saturday, June 25, 2011 6:25 AM
> To: General Discussion of SQLite Database
> Subject: EXT :[sqlite] question about sqlite aggregate funcitons
>
> I create a test database with almost 10 milion rows.
> I'm surprised to see that the first SELECT below is much slower than the
> sum of the next three SELECTs.
>
> Can anyone give a hint why this is the case?
>
> SQLite version 3.7.2
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .timer on
> sqlite> .schema test
> CREATE TABLE test (i integer primary key);
> sqlite> select min(i), max(i), count(i) from test;
> 1|9999999|9999999
> CPU Time: user 4.508429 sys 0.265202
> 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.497610 sys 0.390002
> sqlite>
>
> --
> Luuk
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
sqlite>
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
