SELECT lat, lon, depth FROM hydro 
  WHERE depth>= 49.01 AND depth <=50.99;

The SELECT may be faster still if you use this index instead:

  CREATE INDEX hydro_indx2 ON hydro (depth, lat, lon);

as the query can find all the information in the index without 
hitting the pages of the main table. (At the expense of having 
a larger database file.)

The data in the hydro table need not be presorted at population
unless you need to pull out additional columns not listed in the
index.

Always try to create INDEXes after you populate the table
(where possible) in order to have contiguous table and index
pages.

If you have the inclination you could also experiment with 
various page sizes.



-- 
View this message in context: 
http://www.nabble.com/Adding-index-to-table-makes-SELECT-much-slower.-Why--tp19889143p19908299.html
Sent from the SQLite mailing list archive at Nabble.com.

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to