kamil <[EMAIL PROTECTED]> wrote:
I have a table with ~1 milion records.
CREATE TABLE
(
time INTEGER NOT NULL,
channel INTEGER NOT NULL,
path INTEGER NOT NULL,
file INTEGER NOT NULL,
flags INTEGER NOT NULL,
PRIMARY KEY(channel,time,path,file)
)
CREATE INDEX id_channel_time ON files(channel,time)
This index is superfluous. Your primary key already generates an index
that contains all the same information, and then some.
And I have a two queries:
SELECT time,channel,path,file,flags
FROM files
WHERE channel = ? AND time >= ?
ORDER BY time ASC "
LIMIT ?
SELECT time,channel,path,file,flags
FROM files
WHERE channel IN (-2,?) AND time >= ?
ORDER BY time ASC "
LIMIT ?
It takes <1ms to return 16 rows using the first query, but over 200ms
when using the second one. What is wrong ? Is there a way to speed up
the second query ?
The first query can use index to satisfy ORDER BY clause, the second
query cannot and has to sort on the fly.
Try putting 'time' first and 'channel' second in your primary key
clause - and drop that id_channel_time index.
Igor Tandetnik
