Re: [sqlite] trying to optimize left outer join for large data set, (multiple indices needed?)

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

Re: [sqlite] trying to optimize left outer join for large data set, (multiple indices needed?)

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 inde

Re: [sqlite] trying to optimize left outer join for large data set (multiple indices needed?)

Hi, at the first sight i'd suggest that you reorder the rows of your index : it is most likely that chr and strand will have many equal values in your example - especially chr. When chr is the first field of your Index than the path to find first differences in the btree to find the matching i

Re: [sqlite] trying to optimize left outer join for large data set (multiple indices needed?)

On 9 Jun 2009, at 7:12am, Elizabeth Purdom wrote: > SELECT regions.id, TOTAL(reads.data) FROM regions LEFT OUTER JOIN > reads ON > ( regions.chr = reads.chr AND regions.strand=reads.strand > AND regions.start<=reads.start AND regions.stop>=reads.stop > ) > GROUP BY regions.id ORDER BY regi

[sqlite] trying to optimize left outer join for large data set (multiple indices needed?)

Hello, I'm basically a newbie, but have been plunged into not just having correct code but having to optimize a particular query. The following query works but is very slow on my database with millions of entries in one of the tables. SELECT regions.id, TOTAL(reads.data) FROM regions LEFT OUT