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]
-----------------------------------------------------------------------------
