Geek asks and answers

How to optimize 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 following query is fast enough:

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

But when I add an extra condition to the WHERE clause, the query slows down a lot

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

I tried to create a new index t1(time, "Bytes Received", filename), but the execution speed did not change.

How do I change indexes on a table to speed up a query?

+3
source share
2 answers

Use the BETWEENfollowing 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 SQL strings are enclosed in simple quotes (double quotes refer to tables, schemas, and column names).

+4
source

Benoit , BETWEEN Sqlite, , AND ( , ). , . ( EXPLAIN QUERY PLAN sqlite, .)

Sqlite 1 . , , INTERSECTing:

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'
)

, .

INDEXED BY , " ", ( , - , , , .)

+1

More articles:


All Articles