On 19 Oct 2013, at 4:51pm, Raheel Gupta <raheel...@gmail.com> wrote:
> CREATE TABLE map ( > n BIGINT NOT NULL DEFAULT 0, > s INT(5) NOT NULL DEFAULT 0, > d INT(5) NOT NULL DEFAULT 0, > c INT(1) NOT NULL DEFAULT 0, > b UNSIGNED BIGINT NOT NULL DEFAULT 0 > ); By the way, SQLite has only one INTEGER type: INTEGER. It's signed. All the rest of that is ignored. > CREATE INDEX map_index ON map (n, s, d, c, b); > > The above table is having nearly 600 Million Records and is of size 26 GB. > [snip] > > 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 > > [snip] > After this SQLITE goes into an endless search for some reasons unknown. You might learn using EXPLAIN QUERY PLAN: <http://www.sqlite.org/lang_explain.html> My guess is that SQLite has ended up sorting or constructing an index with many non-qualifying entries at the end of it. For instance after all the entries you do want it might end up with a few million where d=15. And SQLite has to look through them all before concluding that it has finished returning entries for your SELECT. You might do better making an index more suited to your query. I don't know which parts of the above are fixed and which aren't so I can't suggest one, but you might be able to figure one out and check, using EXPLAIN QUERY PLAN, whether SQLite prefers that one to map_index. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
