That's because they have 2 completely different query plans.
I created the table so that id,a,b,c all had the same values so the indexing
would be indentical.
#include <stdio.h>
main()
{
int i;
for(i=1;i<=100000;++i) {
char sql[4096];
sprintf(sql,"insert into abctable(a,b,c) values(%d,%d,%d);",i,i,i);
puts(sql);
}
}
Turns out the if you include the primary key in an index it doesn't use the
triple index but uses the primary key instead. And analyze doesn't change it.
This is with version 3.7.5
But...if you change "c" to be "unique" then it uses an auto_index.
sqlite> pragma cache_size=150000;
sqlite> CREATE TABLE [abctable] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, a, b,
c);
sqlite> begin;
sqlite> .read x.sql
sqlite> commit;
sqlite> create index idxabc on abctable(a,b,c);
sqlite> create index idxabid on abctable(a,b,id);
sqlite> explain query plan select * from abctable where a=10 and b=20 and id=30;
0|0|0|SEARCH TABLE abctable USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
sqlite> explain query plan select * from abctable where a=10 and b=20 and c=30;
0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=? AND c=?)
(~8 rows)
sqlite> analyze;
sqlite> select * from sqlite_stat1;
abctable|idxabid|100000 1 1 1
abctable|idxabc|100000 1 1 1
Re-do with "c unique" in table:
sqlite> explain query plan select * from abctable where a=10 and b=20 and c=30;
0|0|0|SEARCH TABLE abctablsqlite> explain query plan select * from abctable
where a=10 and b=20 and id>30;
0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=?) (~3 rows)
sqlite> explain query plan select * from abctable where a=10 and b=20 and c>30;
0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=? AND c>?)
(~2 rows)e USING INDEX sqlite_autoindex_abctable_1 (c=?) (~1 rows)
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________
From: [email protected] [[email protected]] on
behalf of Max Vlasov [[email protected]]
Sent: Tuesday, July 26, 2011 6:50 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SELECT query first run is VERY slow
On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin <[email protected]> wrote:
>
> On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote:
>
> > This leads us to conclusion: index in SQLITE database if scattered and
> cannot be "jumped directly" to N-th element. SQLITE has to read it somehow
> consecutively.
> >
> > And so SQLITE has to read half of index (!) to find matching index
> record.
>
> I don't think it's SQLite itself that's reading half the index. I think
> it's some part of your operating system that's trying to cache all of your
> database file as SQLite reads lots of different parts of it spread about
> randomly. Unfortunately I don't see how an OS can reasonably do that since
> it will produce the slow speeds you're complaining about.
>
>
Simon,
actually I narrowed down the problem (cmiiw).
The simple table
CREATE TABLE [abctable] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c)
add many records (100,000)
INSERT INTO abctable (a, b, c) VALUES (10, 20, 30)
Good variant
CREATE INDEX idxabc ON abctable (a, b, c)
SELECT * FROM abctable WHERE a=10 and b=20 and c > 1000000
Sqlite reads few data (3k actually for 100,000 records) to show empty result
Another variant
CREATE INDEX idxabid ON abctable (a, b, id)
SELECT * FROM abctable WHERE a=10 and b=20 and id > 1000000
Sqlite reads much (1,7MB)
Checked with 3.7.6.2,
I suppose the latter due to some special meaning of the id/rowid, but I
suppose the second case should work with fewer reads
Max
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
