"Jonathon" <thejunk...@gmail.com> wrote in message news:5dd932e10902271602m59107b2fnb8c9838d12b87...@mail.gmail.com > Here is my query: > > SELECT * FROM tableR r > INNER JOIN tableU u ON u.id = r.u_id > LEFT JOIN tableP p ON u.p_id = p.id > ORDER BY p.p_name; > > tableR has about 5 million records. > tableU has 100k records. > table P has 1k records. > > It's very weird. If I change the last join to an INNER JOIN, > everything works great.
EXPLAIN QUERY PLAN is your friend. With inner join, SQLite scans tableP in order (using the index on name), and for each record looks up a corresponding record in tableU and finally in tableR. The execution time is roughly O(1K). With LEFT JOIN SQLite can't start with the scan on tableP. It arbitrarily chooses to start with tableR instead, and for each record looks up tableU then tableP, and finally sorts the result. So it takes O(5M) time - three orders of magnitude slower. You'd probably see somewhat better speed if you simply interchange tableR and tableU: SELECT * FROM tableU u INNER JOIN tableR r ON u.id = r.u_id LEFT JOIN tableP p ON u.p_id = p.id ORDER BY p.p_name; This way SQLite would linearly scan only 100K records. I don't think you can do any better than that with LEFT JOIN in place - the query has no choice but to look at every record in tableU. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
