On 10 Jun 2009, at 5:15am, Elizabeth Purdom wrote: > 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.
You've got the right idea, but even just using that index for a and b will much reduce the amount of records that have to be iterated down to something a computer can do in a split second. And by including c and d in the index you ensure that the library doesn't have to fetch those values from the record data: it can see all the values it needs in the index it's already looking at. This can save the SELECT command quite a lot of time and effort fetching the record data as it does the 'JOIN'. > 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? Can I urge you first to try your query with the obvious index and find out whether it's too slow ? The sort of stuff we're discussing here is useful in a highly technical context, but you almost never have to worry about that level of detail in real life. You're doing the same thing most programmers do to start off with: trying to wring every possible millisecond out of each query whether it's worth it or not. If you've tried the simple and easy job of having SQLite handle the job, and it's not fast enough, then's the time to consider the options you listed. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
