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