Previous Articles
Topic 1: Force Lookup Join Instead of Merge or Hash Join
To make the optimizer prefer lookup joins over merge or hash joins when performing foreign key checks, set the prefer_lookup_joins_for_fks
session variable to on (merge joins are the default for single-row inserts and hash joins are likely to appear with more rows. There are times when a lookup join will be more efficient than the chosen plan by the optimizer. The lesson here is to test all available options and not to accept the defaults!
set prefer_lookup_joins_for_fks=on;