Hi,
Given the table:
CREATE TABLE Posts (
PostID INTEGER PRIMARY KEY,
CategoryID INT NOT NULL
)
Filled up with 500,000 rows
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?
_____
Regards
Jesús López
