(Уверен что для специалистов это известно, но мне лично было в новинку).

Оказалось , что в новых версиях (5.7, 5.6 может и раньше)
появилось автоматическое индексирование подзапросов.
Не вдаваясь в реальный запрос, дальше рассмотрим
простенький пример на стандартной базе sakila.
По дороге увидим и квери рерайт.

тестовая задача: для всех посетителей выдать количество
продаж от продавцов номер 2 и 22.

javajdbc,

Скорость 16 мс (это Виндовс не может точнее сказать)

# id select_type table partitions type possible_keys key key_len ref rows filtered Extra'1' 'SIMPLE' 'c' NULL 'index' "PRIMARY;idx_fk_store_id;idx_fk_address_id;idx_last_name" 'PRIMARY' '2' NULL '599' '100.00' 'Using index''1' 'SIMPLE' 'r' NULL 'ref' "idx_fk_customer_id;idx_fk_staff_id" 'idx_fk_customer_id' '2' 'sakila.c.customer_id' '26' '50.02' 'Using where'

Теперь тоже самое с подзапросом.
Раньше (до 5.5?) такой запрос был бы медленый,
без никаких шансов использовать индекс




Скорость -- теже 16 мс. План:

# id select_type table partitions type possible_keys key key_len ref rows filtered Extra'1' 'SIMPLE' 'c' NULL 'index' 'PRIMARY;idx_fk_store_id;idx_fk_address_id;idx_last_name' 'PRIMARY' '2' NULL '599' '100.00' 'Using index''1' 'SIMPLE' 'r2' NULL 'ref' 'idx_fk_customer_id;idx_fk_staff_id' 'idx_fk_customer_id' '2' 'sakila.c.customer_id' '26' '100.00' 'Using where'

План#2 идентичен плану #1. МуСКЛ переписал запрос (query rewrite) и раскрыл скобки!

Теперь самое интересное. Добавим ЛИМИТ 1000 в подзапрос.
По идее это заставит МуСКЛ материализовать подзапрос и
ни какого рерайта и использовании индекса не получится.




Скорость 0 мс . значит гдето <5 мс то есть быстрее чем было.
WTF ????
Смотрим план:

# id select_type table partitions type possible_keys key key_len ref rows filtered Extra'1' 'PRIMARY' 'c' NULL 'index' 'PRIMARY;idx_fk_store_id;idx_fk_address_id;idx_last_name' 'PRIMARY' '2' NULL '599' '100.00' 'Using index''1' 'PRIMARY' '<derived2>' NULL 'ref' '<auto_key0>' '<auto_key0>' '2' 'sakila.c.customer_id' '10' '100.00' NULL'2' 'DERIVED' 'r2' NULL 'range' 'idx_fk_staff_id' 'idx_fk_staff_id' '1' NULL '8005' '100.00' 'Using index condition'

Мы таки заставили МуСКЛ материализовать подзапрос, но
оптимизатор автоматически проиндексировал подзапрос!!!!
Обратите внимание как <auto_key0> был использован связке
подзапроса с основным запросом.

--the end--

P.S MySQL 5.7.10

miksoftjavajdbcМы таки заставили МуСКЛ материализовать подзапрос, но
оптимизатор автоматически проиндексировал подзапрос!!!!
Обратите внимание как <auto_key0> был использован связке
подзапроса с основным запросом.Материализацию ряда подзапросов я видел в новый фичах версии 5.7 (в т.ч. в случае WHERE field IN (SELECT ...) ). А вот индексирование вижу впервые.

таки да, это еше с 5.6. Пишут что если сабквери надо материализовать,
то оптимизатор проиндексирует на лету -- если в результате
можно будет соединять по REF и этот REF окажется быстрее чем
другие варианты связки.

dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

автор For cases when a derived table requires materialization, the optimizer may speed up access to the result by adding an index to the materialized table. If such an index enables ref access to the table, it can greatly reduce amount of data that must be read during query execution. Consider the following query:

SELECT *
FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
ON t1.f1=derived_t2.f1;

The optimizer constructs an index over column f1 from derived_t2 if doing so would enable use of ref access for the lowest cost execution plan. After adding the index, the optimizer can treat the materialized derived table the same as a usual table with an index, and it benefits similarly from the generated index. The overhead of index creation is negligible compared to the cost of query execution without the index. If ref access would result in higher cost than some other access method, the optimizer creates no index and loses nothing.


...как-то в давнем споре, я поспорил , кажется с Устиновым
и проиграл яшик пива. Я думал что MySQL сматерилизует и проиндексирует
подзапрос как Oracle.... Похоже что сейчас ресультат спора был бы другим :-)