Raheel Gupta wrote: > > CREATE INDEX map_index ON map (n, s, d, c, b); > > > > SELECT n, c, b, s FROM map WHERE s <= '326' AND s NOT IN (0) AND d = '15' > > AND n >= '15591116' ORDER BY n ASC LIMIT 0, 32768 > > 0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows) > 0|0|0|EXECUTE LIST SUBQUERY 1 > > It seems to be using the the covering index which I guess is the fastest > way as Sqlite doesnt need to check the actual table. So why would it slow > down and use so much CPU in my last query ?
In theory, a multi-column index can be used to speed up searches for values in multiple columns. However, an inequality (like n >= 15591116) can be used only in the _last_ column to be searched in an index, so this is the only column in this particular query. Furthermore, this just speeds up searching for the _first_ record that might match. SQLite still has to go through all the records following that in the index. (On average, this is half of all records.) > I am not sure how can I optimize this ? You should have the column with the equality test first in the index, i.e., "CREATE INDEX xxx ON map(d, n);". Alternatively, an index on the columns d and s might be more efficient. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
