How to optimize an SQLite index?

I have a table t1 and two indexes:

create index if not exists timeindex on t1(time, "Bytes Received")
create index if not exists filenameindex on t1(filename)

The next query executes fast enough:

select "Bytes Received" from t1 where time>="11:19:31.18" a开发者_C百科nd time <= "11:19:36.18"

But when I add an additional condition to WHERE statement the query slows down tremendously

select "Bytes Received" 
    from t1 
    where time>="11:19:31.18" and time <= "11:19:36.18"
    and filename = "SE12.log"

I've tried to create a new index t1(time, "Bytes Received", filename) but execution speed didn't changed.

How should I change the indexes in the table to speed up the query?

---

Use BETWEEN and the following index:

CREATE INDEX IF NOT EXISTS fntimeindex ON t1(filename ASC, time ASC);
SELECT "Bytes Received"
  FROM t1 INDEXED BY fntimeindex
  WHERE filename = 'SE12.log'
   AND time BETWEEN '11:19:31.18' AND '11:19:36.18';

Also note that in SQL strings are enclosed in simple quotes (double quotes are for table, schemas and column names).

---

Benoit's answer is correct in that BETWEEN tells Sqlite a range is coming, so it only has to search the index one time for both terms in the AND expression (otherwise, it would search the index twice, once for the left-hand side, and once for the right). This cuts the number of records looked at in half. (If you run EXPLAIN QUERY PLAN in the sqlite command-line tool, it will clearly show this.)

However, the query planner in Sqlite likes to use 1 index per table. To get around this, the canonical solution is to explicitly rewrite the query as a series of INTERSECTing sub-selects:

SELECT "Bytes Received" FROM t1 WHERE ROWID IN
(SELECT ROWID FROM t1 WHERE filename = 'SE12.log'
INTERSECT
SELECT ROWID FROM t1 WHERE time BETWEEN '11:19:31.18' AND '11:19:36.18'
)

You can obviously add more sub-selects to handle additional terms.

Use of INDEXED BY should not be necessary, since it is not a "hinting mechanism" but rather a hard-requirement (that will cause an error if a schema change makes the index go away -- but perhaps that is what you want.)