I had been involved with subquery optimizations fairly closely, but last week I was surprised to find out that MySQL 5.6 does not support derived table merging. This feature was among the subquery features in the abandoned MySQL 6.0. In MariaDB, it was finished and released as part of MariaDB 5.3/5.5. As for MySQL, neither MySQL 5.6, nor MySQL 5.7 has this feature.
So what is this “derived merge”? It’s simple to understand. When one writes complex queries, it is common to use FROM-clause subqueries as a way to structure the query:
select sum(o_totalprice) from (select * from orders where o_orderpriority=’1-URGENT’) as high_prio_orders where o_orderdate between ‘1995-01-01′ and ‘1995-01-07′
MySQL …[Read more]