Hi, I appreciate the tips about timing the indexing and the order of the variables. However, I am particularly trying to address the following documentation on the SQLite page entitled 'The SQLite Query Optimizer Overview' (http://www.sqlite.org/optoverview.html).
There they say: > If an index is created using a statement like this: > > CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z); > > Then the index might be used if the initial columns of the index (columns a, > b, and so forth) appear in WHERE clause terms. All index columns must be used > with the = or IN operators except for the right-most column which can use > inequalities. For the right-most column of an index that is used, there can > be up to two inequalities that must sandwich the allowed values of the column > between two extremes. > > It is not necessary for every column of an index to appear in a WHERE clause > term in order for that index to be used. But there can not be gaps in the > columns of the index that are used. Thus for the example index above, if > there is no WHERE clause term that constraints column c, then terms that > constraint columns a and b can be used with the index but not terms that > constraint columns d through z. Similarly, no index column will be used (for > indexing purposes) that is to the right of a column that is constrained only > by inequalities. For the index above and WHERE clause like this: > > ... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello' > > Only columns a, b, and c of the index would be usable. The d column would not > be usable because it occurs to the right of c and c is constrained only by > inequalities. I have inequalities on both 'start' and 'stop', which would seem to imply 1) chr and strand must come first and 2) start and stop in the same index won't help. My understanding is that the 'ON ...' phrase is equivalent to the WHERE phrase, so this would be relevant. This seems to also imply to me that if I have inequalities in my query on c and d, that a index (a,b,c,d) would be *used* but only for the parts dealing with a,b, and c and that d would be manually scanned over. So if I do EXPLAIN QUERY, it will say the index is being used, but won't tell me if it is being used for a,b,c, *and* d or just a,b, and c. This is what I make of this, but I'm not sure if this is correct. So I don't what do do: can I can keep my same query, make separate indices (chr,strand,start) and (chr,strand,stop) and have them both be used, or do I need to rewrite my query into two parts so that the two indexes can be used and if so how? I'm thinking the later, because of tips I read from elsewhere that seem to say only ONE index can be used per WHERE clause (and had suggestions about to break up a WHERE clause into two and then intersect, as I mentioned on the original post, but again I don't know how this works for the OUTER JOIN). Thanks, Elizabeth Purdom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
