RE: [sqlite] Query Optimizer and INTEGER PRIMARY KEY

[Jesús López]

Dennis,

Thanks for your reply. 

Sorry for the typo, I wrote it directly on Outlook and I did not check it. 

I will fill a bug report (a ticket)

Regards:

Jesús López


-----Mensaje original-----
De: Dennis Cote [mailto:[EMAIL PROTECTED] 
Enviado el: martes, 19 de diciembre de 2006 16:30
Para: sqlite-users@sqlite.org
Asunto: Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY

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)
>
> The query:
>
> SELECT CategoryID, PostID
> FROM Posts
> ORDER BY CategoryID
> LIMIT 10
>
> Takes about 15 ms to complete.
>
> However the query:
>
> SELECT CategoryID, PostID
> FROM Posts
> ORDER BY CategoryID, PostID
> LIMIT 10
>
> Takes about 1200 ms to complete. Almost one hundred times slower.
>
> This seems to me odd behavior, because I have specified both CategoryID
and
> PostID in the index IX_Posts. SQLite is sorting the rows, and it is not
> taking advantage of the index.
>
> If I define the table like this:
>
> CREATE TABLE Posts (
>     PostID LONG PRIMARY KEY,
>     CategoryID INT NOT NULL
> )
>
> both queries takes about 15 ms, which is even more amazing.
>
> What is hapening here? Why SQLite behaves like if the INTEGER PRIMARY KEY
> field was not in IX_Posts index?
>
>   
Jesus,

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.

Dennis Cote


----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-

smime.p7s
Description: S/MIME cryptographic signature