On 19 Oct 2013, at 6:54pm, Raheel Gupta <raheel...@gmail.com> wrote:
> Here is the output : > 0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows) > 0|0|0|EXECUTE LIST SUBQUERY 1 > > I am not sure how can I optimize this ? > (Also I checked again and there are 4166 rows in this last result and not > 1568 as per my last email.) > > 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 ? I cannot definitely solve your problem but I can think of some things to try. First, do these: ANALYZE; CREATE INDEX map_dsn ON map (d, s, n); CREATE INDEX map_dns ON map (d, n, s); then execute the same SELECT. Does it have the same problem ? Does the EXPLAIN QUERY PLAN tell you which of the (now) three indexes SQLite has chosen. Just in case you didn't know, you can download the SQLite command-line tool and execute SQL commands in it. This means you don't have to change your own software. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
