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);
With 3.5.7, SQLite seems to use the primary key:
0|0|TABLE pets
1|1|TABLE owners USING PRIMARY KEY
However, 3.5.8 appears to do a full table scan:
0|0|TABLE pets
1|1|TABLE owners
On my actual database, the query time jumped from a couple milliseconds
to a few seconds. In the meantime, I can continue using the earlier
version(s). However, I wanted to let others take a look, to see if the
issue was with my query (quite possible), or with the new version.
Thank you,
Eric
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
