Hi, Here are the results for DBT3 query set I've got on my laptop with MariaDB 5.3.0 installed. [ Dell Latitude E6510: 4-cores with multithreading (8 threads in total) Memory: 8GB, SSD, SuSE 11.3 ] DBT3 factor 10 (60M rows in lineitem) built for InnoDB. Mysqld was launched with the following parameters: --innodb-buffer-pool-size=1G --innodb-additional-mem-pool-size=20M --innodb-log-file-size=256M --innodb-file-per-tab --innodb_file_format=barracuda Each query was executed 1. with default optimization options (as they are set in mysql-5.1/5.5) 2. with the best optimization options found for the query I would like to attract your attention to the fact that hash join can really compete with BKA join for some queries (see Q9, Q12, Q13). Also: EXPLAIN for Q7, Q8, Q9 now is instantaneous. Q18 practilly cannot be executed without optimizations. My estimation tells me that it would take ~50 years to execute it unless the in_to_exists transformation is optimized for group by queries (then it will take only ~80 hours). See how effectively optimize_join_buffer_size can be used for Q9. (similar join buffer optimization could be applied for other queries, I just did not do it). Enjoy the numbers below. Regards, Igor. ==================================================== time in seconds time in seconds ratio w/o optimizations with optimizations Q1 172 - [172] 1.0 Q2 112 31 3.61 Q3 189 197 0.96 Q4 417 103 4.05 Q5 416 137 3.04 Q6 119 - [119] 1.0 Q7 558 221 2.52 Q8 1023 338 3.02 Q9 2399 218 (265) 11.00 (9.05) Q10 542 139 3.90 Q11 33 25 1.32 Q12 308 247 1.25 Q13 446 133 3.35 Q14 302 92 3.28 Q15 1334 164 8.13 Q16 45 10 4.5 Q17 46 51 0.90 Q18 [*] 252 (218) - Q19 76 63 1.21 Q20 463 337 1.37 Q21 297 304 0.98 Q22 11.02 10.72 1.03 ==================================================== optimization settings used Q1 [none] Q2 set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*64; set join_cache_level=6; Q3 set join_buffer_space_limit=1024*1024*256; set join_buffer_size=1024*1024*32; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q4 set optimizer_switch='mrr=on'; set mrr_buffer_size=1024*1024*8; Q5 set optimizer_switch='mrr=on'; set mrr_buffer_size=1024*1024*8; set join_buffer_space_limit=1024*1024*1024; set join_buffer_size=1024*1024*32; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q6 [none] Q7 set optimizer_switch='derived_merge=on'; set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*1024; set join_buffer_size=1024*1024*64; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q8 set optimizer_switch='derived_merge=on'; set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*1024; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q9 set optimizer_switch='derived_merge=on'; set optimizer_switch='optimize_join_buffer_size=on'; set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; ( set optimizer_switch='derived_merge=on'; set optimizer_switch='optimize_join_buffer_size=on'; set join_buffer_space_limit=1024*1024*32; set join_buffer_size=1024*1024*24; set join_cache_level=4; ) Q10 set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q11 set optimizer_switch='mrr=on'; set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*8; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q12 set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*512; set join_cache_level=4; Q13 set tmp_table_size=1024*1024*32; set max_heap_table_size=1924*1024*32; set optimizer_switch='outer_join_with_cache=on'; set join_buffer_space_limit=1024*1024*16; set join_buffer_size=1024*1024*8; set join_cache_level=4; Q14 set optimizer_switch='mrr=on'; set mrr_buffer_size=1024*1024*8; set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*32; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q15 set tmp_table_size=1024*1024*32; set max_heap_table_size=1924*1024*32; set optimizer_switch='derived_with_keys=on'; set optimizer_switch='mrr=on'; set mrr_buffer_size=1024*1024*32; set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*16; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q16 set optimizer_switch='materialization=on'; Q17 set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*16; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q18 set tmp_table_size=1024*1024*32; set max_heap_table_size=1024*1024*32; set optimizer_switch='materialization=on'; set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; ( set tmp_table_size=1024*1024*32; set max_heap_table_size=1024*1024*32; set optimizer_switch='materialization=on'; set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*128; set optimizer_switch='mrr=on'; set join_cache_level=4; ) Q19 set join_buffer_space_limit=1024*1024*128; set join_buffer_size=1024*1024*16; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q20 set optimizer_switch='semijoin=on'; set optimizer_switch='materialization=on'; set optimizer_switch='in_to_exists=off'; Q21 set join_buffer_space_limit=1024*1024*512; set join_buffer_size=1024*1024*64; set optimizer_switch='mrr=on'; set optimizer_switch='mrr_sort_keys=on'; set join_cache_level=6; Q22 set optimizer_switch='derived_merge=on';