Re: select with join of 25 tables crashes mysql database
Posted by:
Rick James
Date: October 05, 2012 11:03AM
> That said, I'm impressed by the performance RJ was able to provide - but are you sure that's not due to some kind of caching?
The optimizer tries all combinations of join order. As it goes through, it prunes based on cost. However, since the tables are so similar, the pruning does not achieve much. I think the effort goes up as the factorial of the number of tables (plenty fast).
By setting optimizer_search_depth to something much less than the default of 62, you are telling the optimizer to give up on finding the "absolute best" and simply "pick something!". I picked 2 out a hat, guessing:
* Bigger would be slower
* =1 might prevent too much optimizing, especially assuming one of the tables is different.
* =2 might be optimal.
I would guess that 99% of SELECTs join fewer than 5 tables.
A 4-table join (where the tables are not all alike) might benefit from a value of 4 (or more). A value of 2 might speed up the optimizer a tiny bit, but might lead to a sub-optimal join order.
This example adds fuel to my thought that the default value for optimizer_search_depth should be a smaller value, like 5.
Don't feel bad, laptop. This is a very obscure setting, but it happened to be discussed in a talk at Oracle's MySQL Connect last weekend, so it was fresh in my mind.