How will that help him fix this problem, if the problem is that SQLite's query optimizer is selecting a suboptimal index to use, and there is no way to specify which index to use?
----- Original Message ----- From: "Dan Kennedy" <danielk1...@gmail.com> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Friday, August 14, 2009 11:15 PM Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which to use > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
