|Subject||RE: [firebird-support] LEFT JOIN much faster than JOIN|
|Author||Svein Erling Tysvær|
>To understand what's happening, you must look at the plan for the join.I always think of it the opposite way, that is, in case of a LEFT JOIN that the LEFT (inner) table is taken first and then the RIGHT (outer) table. But that is just my impression from reading plans, and your 30 or so years of intimate knowledge of InterBase/Firebird makes contending your description something like a passenger of a car trying to disagree with the mechanic that actually designed and implemented the car, so I won't do that.
>An outer join forces the order of the operation - outer table first, then
>inner table. Depending on the indexes and indexed conjuncts, the order
>you set in the outer join may lead to a fast join, or not. If outer joins
>work well for you, I'd guess that you know your data and that (maybe) the
>statistics on your indexes are bad. In theory, the optimizer should choose
>the best order for inner joins, but if you have better information than it
>does, outer joins will be faster.
[The rest is off topic]
<commercial note>(if you need more than luck, try IBPhoenix)</commercial note>
I'd recommend a slightly different route, since I consider Firebird a fairly simple system (from the user point) that doesn't normally require an expert to get things up and running:
1) Use common sense and implement something
2) In case of poor performance, identify the problem and try to fix (which may include JOIN/LEFT JOIN)
3) In case of questions or unsolved issues, search the internet or this list for answers
4) If still wondering or suboptimal performance, write a short and clear example describing your issue and ask on this list (poor questions generate poor answers, so there's no point in asking unless you can convey your problem)
5) If still not OK or the problem is too complex to formulate clearly in a reasonably short email, get professional help from IBPhoenix or similar
If you are on a tight schedule, have a large or critical system or want to be certain that the system will work properly, then it is of course worth considering using IB Phoenix right from the start, they do know everything worth knowing about Firebird.