On Aug 15, 2009, at 1:08 PM, His Nerdship wrote: > > Good day, > We have a puzzling problem with a large (1GB+) database. > Most of our queries are based on 3 columns, say X, Y and Z. > X is always the first in the index. However, sometimes the query > involves a > small range of Y and a larger range of Z, and sometimes the > reverse. We > first had an index based on X, Y & Z (in that order), and noticed > that the > results are fast when there was one X, one Y and many Z's. I check > if the > range is a single value, and if it is, I change the SQL to a > straight '=', > e.g: > ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20; > > According to Mike Owens, using an equality or IN operator on Y > allows Z to > be indexed, speeding up the search. If Y is a range and we use > "BETWEEN y1 > AND y2" on it, then Z will not be indexed. This is what we found - > the > second search was much slower. > > However because sometimes the numbers are reversed, such that there > are many > Y's and few Z's, we added another index based on X, Z and Y, in that > order. > In this case, though, it didn't make any difference. It seems like > SQLite > does not select the correct index to use - it uses XYZ instead of XZY.
You might just need to run the ANALYZE command to collect database statistics. http://www.sqlite.org/lang_analyze.html Use EXPLAIN QUERY PLAN to see the strategy SQLite is using for each query. http://www.sqlite.org/lang_explain.html Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
