--- 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) > > > 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 ? I think you'll have to do this: SELECT time,channel,path,file,flags FROM files WHERE channel = -2 AND time >= ?1 UNION SELECT time,channel,path,file,flags FROM files WHERE channel = ?2 AND time >= ?2 ORDER BY time ASC " LIMIT ?3 Investigate EXPLAIN and EXPLAIN QUERY PLAN to gain more insight. Dan. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
