Fun MySQL fact of the day: first match optimisation

If we consider back to Tuesday, one of MySQL's guarantees about subqueries is that it will not return the same row in the outer query twice. Compare this to a join, which will return a copy of the left-hand-table's row for each matching right-hand-table row.

Except, we've already identified that everything in MySQL is a join. So, you may be wondering: if a subquery is just a join, why don't we get duplicate rows from the outer table? It's because there are 3 separate subquery optimisations that MySQL can employ. Today, we're going to look at one of the three: the FirstMatch strategy. When you use a subquery eligible for FirstMatch optimisation, MySQL will perform a nested loop join, as usual, as we've discussed in the past. But, with FirstMatch, after MySQL finds a matching join record of the left-hand-table in the right-hand-table, MySQL will abort the inner loop and continue to the next record of the outer loop. This prevents the storage engine from retrieving countless unnecessary rows that never even get used.

It really is this simple, and you can see it happening when the Extra column of the explain for your subquery shows FirstMatch(outer_table_name).