First of all I must say that I enjoyed reading your presentation at http://www.sqlite.org/php2004/page-001.html on the internals of SQLite and the performance tips. I could not find any information on the processing of LEFT JOIN queries though...
As SQLite only does loop-joins, I wonder how I should best use LEFT JOINS. Most of my queries use LEFT JOINS to get additional information on - in this case - specific aircraft. 2 Tables "asterix" and "aircraft" with indexes: CREATE INDEX a_modea ON asterix(modeA); CREATE INDEX a_modes ON asterix(modeS); CREATE INDEX b_modes ON aircraft(modeS); the first table (A) contains approx. 4.000.000 records (several hundred entries per aircraft) while the second table (B) contains about 100.000 records (lookup table with aircraft details) total database size is approx. 3GB. (there are 2 other tables in the db similar in size to the first table) the following query takes a long time: select A.ModeA,A.modeS,A.acid,count(A.modeS) plots,B.actype,B.registration from asterix A LEFT JOIN aircraft B ON A.modeS = B.modeS where A.ModeS is not NULL and A.modeA=1234 group by A.ModeS having plots > 5 How does SQLite handle LEFT JOINs ? Is there a better way to write such queries than using LEFT JOIN? Is there a performance benefit in using a combined index like for example: CREATE INDEX a_full ON asterix(modeA,modeS); any tips regarding LEFT JOINs are much appreciated regards, Filip ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
