Реальные примеры оптимизации запросов

Замена IN на JOIN

Dinamique CMS, файл cms/core/MetaData/MetaData.inc.php.

До оптимизации - 42 сек:

SELECT DISTINCT(value) FROM wc_field_joins AS fj WHERE fj.id IN (SELECT DISTINCT(mark) FROM wc_ddata_catalogItem WHERE `parent` = '40' AND `visible` = '1')

После оптимизации - 0 сек:

SELECT DISTINCT(value) FROM wc_field_joins AS fj
JOIN wc_ddata_catalogItem AS ci ON (ci.parent = '40' AND ci.visible = '1' AND ci.mark = fj.id);