* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-11 20:35]: > I think perhaps the correlated subquery optimization is really > another name for rewriting it so the smallest table is the > driving table. It probably doesn't matter how you write the sql > as long as you get the smallest table as the driving table.
Roughly. You can also get a boost if you can rephrase a correlated subquery to a mere EXISTS condition, and there are a few other cases. OTOH sometimes a correlated subquery that just collects data is faster to execute or more readily optimisable when expressed as a JOIN. I remember such a case, but it wasn't in my code so I paid insufficient attention and now my memory of the details is hazy. Generally, I'd approach this the same way as I approach programming in general: try to write the cleanest, most self- documenting code possible, and if practice shows there is a performance problem, then run benchmarks to see what might work better. I see little point in microoptimisations, particularly in absence of a clear need for performance. Regards, -- Aristotle Pagaltzis // <http://plasmasturm.org/>