Richard, Thanks for your reply.
I have tried CREATE INDEX IX_Post ON Post(CategoryID) and I have got the same results. I did it before posting my first post :-), I guessed integer primary key field is in every index, but not sure. Regards: Jesús López -----Mensaje original----- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: martes, 19 de diciembre de 2006 16:48 Para: sqlite-users@sqlite.org Asunto: Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY Dennis Cote <[EMAIL PROTECTED]> wrote: > Jesús López wrote: > > Given the table: > > > > CREATE TABLE Posts ( > > PostID INTEGER PRIMARY KEY, > > CategoryID INT NOT NULL > > ) > > > > And the index: > > > > CREATE UNIQUE INDEX IX_Posts (CategoryID, PostID) > > > > Your create index statement is invalid as posted, it should be > > CREATE UNIQUE INDEX IX_Posts ON Posts(CategoryID, PostID) > > But it looks like you have found a real bug in the optimization code. > You should create a ticket at http://www.sqlite.org/cvstrac/tktnew. > SQLite should be able to execute these queries without even accessing > the Posts table itself since all the required data is available in the > index in the correct order. > 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) -- D. Richard Hipp <[EMAIL PROTECTED]> ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- -
smime.p7s
Description: S/MIME cryptographic signature
