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.
$ 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 "address@hidden: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.