> Is there any way the code can 'suggest' SQLite use a certain index? Does INDEXED BY clause work for you? http://www.sqlite.org/lang_select.html
Pavel On Sat, Aug 15, 2009 at 2:08 AM, His Nerdship<slgdoug...@optusnet.com.au> 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. > I know Mr Hipp is reluctant to add the ability to specify which index to use > - it is 'un-RDBMS like' in his words. > Is there any way the code can 'suggest' SQLite use a certain index? Or at > least confirm which index is being used? > > Also, is there a sensible maximum no of values we can put in an IN clause? > Many of the queries involve all records over a month, and hitherto we have > used, say, ..WHERE Date BETWEEN 20090701 AND 20090731; > In this case would it work better with ..WHERE Date IN (20090701, 20090702, > 20090703, ..., 20090731)? > Thanks in advance > -- > View this message in context: > http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981846.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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
