Search code examples
mysqlindexingexplain

What does FirstMatch mean in a EXPLAIN statement?

Today i noticed a "new" Extra type in a EXPLAIN of a statement.

This is my query

SELECT UrlId,'61','Australia' 
FROM dbase.phonenumbers
WHERE UrlId IN (SELECT UrlId FROM dbase.phonenumbers
                WHERE PhoneNumber LIKE '+31%')
AND PhoneNumber LIKE '+61%'

And the EXPLAIN gives this,

phonenumbers    range   UrlId_PhoneNumber,PhoneNumber   PhoneNumber        12                               4176    Using where
phonenumbers    ref     UrlId_PhoneNumber,PhoneNumber   UrlId_PhoneNumber   4   dbase.phonenumbers.UrlId    4       Using where; Using index; FirstMatch(phonenumbers)

What does exactly FirstMatch(phonenumbers) mean? I never seen it before and couldnt find anything quick enough to explain it.

Solution

  • It is a strategy that avoids the production of duplicates by short-cutting execution as soon as the first genuine match is found.

    So FirstMatch(phonenumbers) means that as soon as we have produced one matching record combination, it will short-cut the execution and jump back to the phonenumbers table.

    From FirstMatch strategy

    • The FirstMatch strategy works by executing the subquery and short-cutting its execution as soon as the first match is found.
    • This means, subquery tables must be after all of the parent select's tables that are referred from the subquery predicate.
    • EXPLAIN shows FirstMatch as "FirstMatch(tableN)".
    • The strategy can handle correlated subqueries.
    • But it cannot be applied if the subquery has meaningful GROUP BY and/or aggregate functions.
    • Use of the FirstMatch strategy is controlled with the firstmatch=on|off flag in the optimizer_switch variable.