On Apr 17, 2008, at 12:04 PM, Eric Minbiole wrote: > I have been using SQLite for about a year now, and have been extremely > pleased. While testing 3.5.8, I noticed that a SELECT with LEFT OUTER > JOIN seemed to stop using an index, resorting to a (slow) full table > scan. A simple (contrived) example follows: > > CREATE TABLE pets ( > pet_id INTEGER PRIMARY KEY, > owner_id INTEGER, > name TEXT ); > > CREATE TABLE owners ( > owner_id INTEGER PRIMARY KEY, > name TEXT ); > > INSERT INTO owners (owner_id, name) VALUES (1, 'Steve'); > INSERT INTO pets (owner_id, name) VALUES (1, 'Fluffy'); > > EXPLAIN QUERY PLAN > SELECT pets.name, owners.name > FROM pets > LEFT OUTER JOIN owners > ON (pets.owner_id = owners.owner_id);
Your work-around until I fix this is to say owners.owner_id = pets.owner_id instead if what you have. In other words, put the table on the left side of the join before the equals sign instead of after it. It shouldn't make any difference. SQLite should generate exactly the same code regardless of whether you say A=B or B=A. Clearly something is busted. It will be fixed soon. D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users