MySQL 6.0 vs 5.1 in TPC-H queries

Last week I played with queries from TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries (from 22) have significantly better execution time (about them in next posts), but I want to write about is queries that execute slower in new MySQL 6.0 version.

Query is pretty simple

with execution plain (in 5.1)

against table

So this query takes 32.27 sec to execute in 5.1, and 39.39 sec in 6.0 (after warpm-ups and fully cached in key_cache)

What is difference in 6.0? Here is execution plain for 6.0:

See there are new Extra notes
USING INDEX condition; USING MRR.

It would be interesting to know what is ‘Using Index condition’, what I was able to find is Baron’s bug with no mention in MySQL 6.0 manual. No mentions about Using MRR also, but I guess it is Multi-Range-Read feature which allows to read data not row-by-row but in batches reads.

So let us see how perform 5.1 and 6.0 in cold mode (I dropped OS caches by

and restarting mysqld)

5.1 – time 1 hour 14 min 36.90 sec
6.0 – time 1 hour 6 min 2.21 sec

So difference is not so big we could speak about big improvements with using MRR here.

What is interesting is iostat -dx 5 output during query execution.

For 5.1 I had: