When used with the index, the query may be producing too many random (slow) disk seeks into your 2GB+ database table. Increasing the page cache substantially may help.
But if the rows of the main table were accessed in order it might also reduce the number of page seeks. Out of curiosity, without changing the page cache size, how long does this query take with the index in place? SELECT lat, lon, depth FROM hydro where rowid in ( select rowid from hydro WHERE depth>= 49.01 AND depth <= 50.99 ); If that doesn't work, you might try populating the hydro table with data sorted by depth in order to keep the data locality in check and disk seeks to a minimum. -- View this message in context: http://www.nabble.com/Adding-index-to-table-makes-SELECT-much-slower.-Why--tp19889143p19894289.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
