"Derek Scherger" <de...@echologic.com> wrote in message news:e97446630903081908l51d3303x507f72865fde1...@mail.gmail.com > 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? > > -- 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
When all columns in the SELECT come from the index, SQLite can get all their values directly from the index and avoid reading actual table. So it saves an index-to-table lookup. Note also that, as far as SQLite is concerned, an index on revision_certs(id) is no better than one on revision_certs (name, ...). You have an extrinsic knowledge that there are many more duplicate names than there are duplicate ids, but SQLite doesn't. Thus, all other things being equal, the point I mentioned above gives an edge to revision_certs (name, ...). > -- 3. the same query as in 2, but with all columns selected using * > again picks the expected index I'm not sure why you call revision_certs(id) an "expected" index. Why do you expect SQLite to pick this particular index? > The id index is much > more selective that the unique index. You may know that, but SQLite doesn't. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
