There are so many exciting new features in MySQL 5.6 that I almost don't know where to start. To mention a few, MySQL's multi-core scalability has been significantly improved to meet modern hardware, InnoDB has better index statistics, much better performance, and online ALTER, replication has multi-threaded slaves and …[Read more]
In a previous post, I had demonstrated how
subquery materialization, introduced in
MySQL 5.6.5, improves the performance of certain queries, like
query Q16 of DBT3. Such improvement was easily explained:
- Subquery materialization has a high start up cost (it needs to create and fill the temporary table).
- But afterwards it has fast lookups (temporary table has a hash index, no duplicates, and is in memory).
- In other words, compared to EXISTS, the first evaluation of the IN predicate is slow (high start up cost) and all following evaluations are fast (just a hash lookup).
- In the DBT 3 setup, one outer table (named "part") has 200,000 rows, so there are 200,000 evaluations of …
In a previous post, I analyzed how a query of the
famous DBT3 benchmark was
optimized by MySQL. It was this query, named "Q16" in the DBT3 jargon:
count(distinct ps_suppkey) as supplier_cnt
p_partkey = ps_partkey
and p_brand <> 'Brand#23'
and p_type not like 'LARGE PLATED%'
and p_size in (43, 1, 25, 5, 35, 12, 42, 40)
and ps_suppkey not in (
s_comment like '%Customer%Complaints%'
Here is a brief recap of conclusions I had drawn: