Sorry, scratch that. T2 was not a virtual table when I ran this query. My bad. Told you I was a noob.
Eric -----Original Message----- From: Eric Hill Sent: Wednesday, May 20, 2015 12:32 PM To: 'General Discussion of SQLite Database' Subject: RE: [sqlite] Virtual Table query - why isn't SQLite using my indexes? Eric had said: >> But then what about a query like this: >> >> SELECT * FROM T1 >> LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = >> T1.b ) AND ( T2.c = T1.c ); >> >> xBestIndex will get called here for T1 with 3 constraints, c, b, and a, in >> that order. To which Gunter replied in part: > an Index on the LHS Table of a LEFT OUTER join never helps, because > you are requesting to retrieve all rows of the LHS anyway. SQLite is calling > xBestIndex with constraints on T1 for symmetry purposes only (this may even > be considered a bug). > Try again with indexes on T2. Very good point, so this is what puzzles me: xBestIndex is *never called* for T2 for this query (!). To my undoubtedly noob mind, *that* seems like a bug. Thanks again! Eric
