[EMAIL PROTECTED] wrote:
In SQLite, every index has the INTEGER PRIMARY KEY as an implied
final column. So an index on
Post(CatagoryID, PostID)
Is really an index like this:
Post(CategoryID, PostID, PostID)
In other words, the extra PostID column is redundant. If you
omit it all together, you run less risk of confusing the optimizer.
Why don't you try defining the index as
CREATE INDEX IX_Post ON Post(CategoryID)
Richard,
Even using the index you suggested, SQLite still does a sort for the
second query. It doesn't use the index at all. This still seems like a
bug in the optimizer.
Also, I would have thought that SQLite would automatically optimize away
the redundant storage of the rowid in an index if it was also storing an
integer primary key column. The users shouldn't have to know how SQLite
implements these features internally in order to define their tables and
indexes without redundant data being stored.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
