If I set set optimizer_switch='block_nested_loop=off' as suggested here, can i get 100% certainty the same result for option on and off ?
I want to change this option to off, because it increases query performance in my case from 56s to 1s.
What are the pros and cons for this optimizer switch, is it safe?
Yes, it is safe. The optimizer_switch tells MySql how to search for the answer to the query. Regardless of how optimizer_switch is set, it will generate the same result for your query (unless there are bugs in MySql).
The only disadvantage to using set optimizer_switch='block_nested_loop=off' is that other queries might become slower, so you might want to set it back to on after executing your query.
