It seems like sqlite (3.6.6.2) chooses different indexes depending on which
columns are selected for *output* and I wonder whether this is a bug?
In some cases monotone experiences poor performance because of this
behaviour (which we can easily work around) but I thought I'd check and see
if this is intentional behaviour or not.
Here's an example:
$ sqlite3 test.mtn
SQLite version 3.6.6.2
-- 1. selecting specific columns using an indexed column as the query key
chooses the expected index
sqlite> explain query plan select id,name,value,keypair,signature from
revision_certs where id = X'852525cb46c3c10969a6b95c2af75f090cbdf796';
0|0|TABLE revision_certs WITH INDEX revision_certs__id
-- 2. adding a second query key (in this case name) changes which index gets
selected
sqlite> explain query plan select id,name,value,keypair,signature from
revision_certs where id = X'852525cb46c3c10969a6b95c2af75f090cbdf796' and
name ='changelog';
0|0|TABLE revision_certs WITH INDEX sqlite_autoindex_revision_certs_2
-- 3. the same query as in 2, but with all columns selected using * again
picks the expected index
sqlite> explain query plan select * from revision_certs where id =
X'852525cb46c3c10969a6b95c2af75f090cbdf796' and name ='changelog';
0|0|TABLE revision_certs WITH INDEX revision_certs__id
The table in question here is declared with:
CREATE TABLE revision_certs
(
hash not null unique, -- hash of remaining fields separated by ":"
id not null, -- joins with revisions.id
name not null, -- opaque string chosen by user
value not null, -- opaque blob
keypair not null, -- joins with public_keys.id
signature not null, -- RSA/SHA1 signature of "[n...@id:val]"
unique(name, value, id, keypair, signature)
);
CREATE INDEX revision_certs__id ON revision_certs (id);
I assume sqlite is choosing the unique index when I've specified both name
and id because name comes first in that index. However it seems odd that it
will choose the id index if I select columns with "*" and the unique index
if I select specific columns. The id index is much more selective that the
unique index. There will be thousands of rows with the same name and only
half a dozen for any given id.
Thanks for any info.
Cheers,
Derek
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
