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
