On Sun, Mar 8, 2009 at 9:46 PM, Igor Tandetnik
<address@hidden> wrote:
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.
Thanks, that helps.
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?
Only because it was the one that *I* was expecting I guess.
I'm curious though, why does 'select *' not also use the unique index on the same grounds that it could get all the values it needs from the index and avoid the table lookup? Does it not know what '*' means without looking into the table?
Would re-ordering our unique index to be (name, id, value, ...) rather than (name, value, id, ...) allow a query with only name and id to use it more effectively? I' wondering if the problem is that value is ahead of id in the index and we can't use anything past name because we don't have a value, whereas if both name and id were available and in the leading edge of the index both could be used. I'm not sure if we ever query by name and value, if we do this may not be an option but it might be.
> The id index is much
> more selective that the unique index.
You may know that, but SQLite doesn't.
Fair enough. I wasn't sure how much sqlite might know about the distribution of actual data in the table and what its query optimizer/planner might do with such information. Probably I've spent too much time looking at the various rule and cost based optimizers in oracle/sybase/sql-server over the years and was expecting a bit much. ;)
Thanks,
Derek