Hi, I'm trying to optimize a query for 2 different scenarios, and I'm having trouble getting something that works good in general. I want to be sure I'm not missing something. Here are the tables and indexes used in my database:
sqlite> CREATE TABLE path_table (idx INTEGER PRIMARY KEY, path TEXT UNIQUE); sqlite> CREATE TABLE val_table (idx INTEGER, val REAL, time INTEGER); sqlite> CREATE INDEX time_idx ON val_table (time ASC); sqlite> CREATE INDEX path_idx ON val_table (idx ASC); 'path_table' contains unique string path names, while 'val_table' records any number of values associated with each path, and the time at which the value occurred. My query looks something like: sqlite> SELECT val FROM val_table WHERE idx IN (SELECT idx FROM path_table WHERE path GLOB '[a]') ORDER BY time ASC LIMIT [b]; where [a] and [b] are provided by my code's caller. My problem occurs when the idx value(s) selected from 'path_table' match a large number of records in the database - say, 100,000 out of 1,000,000 records. In that case, the query takes several minutes to complete even when [b] is small. Presumably it's first looking up all 100,000 rows where 'idx' matches, then applying the ORDER BY clause to those results without indexing. EXPLAIN QUERY PLAN confirms: 0|0|TABLE val_table WITH INDEX path_idx I tried adding "INDEXED BY time_idx", which greatly improved this particular case, because statistically 1/10 rows will match 'idx' and therefore we find [b] of them very quickly when [b] is small. But this hurts performance in other cases, since if there are only a few rows with a matching 'idx', the query ends up manually walking through most of the table. My question: how can I optimize this kind of query so that it utilizes both indexes, to grab the first [b] rows (ordered by time) which also match [a]? Or am I just going to have to guess at which way will be faster, and use "INDEXED BY" to force it? (The documentation says I shouldn't have to do this) Thanks for the help! -- Matthew L. Creech _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users