Hi! For a long time I have been using LEFT JOINs rather than JOINs everywhere, because of some past experience with Firebird 1.5 that LEFT JOIN is much faster than JOIN, even if I have to use WHERE key_of_the_joined_table IS NULL afterwards. Today I encountered a case where this is the case even with both Firebird 2.1 and 2.5. What could possibly be the cause? Should I stick to LEFT JOINs?
The query is: SELECT MAX(date_of_operation) FROM table1 [LEFT] JOIN view1 ON view1.foreign_key=table1.primary_key WHERE table1.some_field=1234 with view1 defined as a simple sequence of LEFT JOINs: SELECT ... FROM table2 LEFT JOIN table3 ON table3.primary_key=table2.foreign_key1 LEFT JOIN table4 ON table4.primary_key=table2.foreign_key2 ... LEFT JOIN tableX ON tableX.primary_key=table4.foreign_key The LEFT JOIN version takes some milliseconds to perform, the JOIN version takes over 25 seconds. The difference in plans is, as far as I can tell, that the LEFT JOIN version first selects the relevant records from table1 and then joins the matching records from view1 to it, while the JOIN version first builds the view1 (all records), then joins table1 to it, and then performs the WHERE: LEFT JOIN: PLAN JOIN (table1 INDEX (index_fk1), JOIN(JOIN(...))) JOIN: PLAN JOIN(JOIN(JOIN(...)), table1 INDEX (index_pk)) (Note: all the joins are indexed) In this case I will simply rewrite the query to use the LEFT JOIN and probably keep using LEFT JOINs in all future queries, even if the JOIN might seem more suitable to the query, but I am really curious why is there such a difference in the optimizer. Thanks, Josef ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
