如果不使用First Match策略,当处理到Country表上满足" Country.continent='Europe' "条件的德国(Deu)记录时,会扫描City表上满足" City.Population > 110001000 AND City.Country='DEU' "的所有记录,再根据匹配记录总数返回"是否匹配"结果:
如果使用First Match策略,当处理到Country表上满足" Country.continent='Europe' "条件的德国(Deu)记录时,会扫描City表上满足" City.Population > 110001000 AND City.Country='DEU' "的第一条记录"Berlin"后,立即返回"是否匹配"结果:
在MariaDB上使用First Match策略的查询的执行计划为:
MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 1*1000*1000) and Country.continent='Europe';
MySQL [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 1*1000*1000) and Country.continent='Europe';
explain select * from Country where Country.code IN (select City.Country from City where City.Population > 0.33 * Country.Population and City.Population > 1*1000*1000)\G