Benchw Results - Mysql Indexes

Index Tuning

This is a study for index tuning of Mysql (5.0.1). The first three iterative index suggestions were made by Peter Zaitsev <peterNOSPAM@mysql.com >. The actual results were compiled by Mark Kirkwood <markir@paradise.net.nz > - all mistakes are his!

As usual Mysql was compiled from source using gcc 3.3.4.

Platform

The usual platform was used

Mobo/CPU/Memory	Tyan Tiger 133 2xPIII 700Mhz 2G
IO	2x40G 7200 RPM ATA-133 PROMISE TX2000 RAID 0
OS	FreeBSD 4.10

The Results

All results were for size = 1 (the default)

Case 0 - Standard Indexes

The results using the standard indexes are:

Test	Run 1	Run 2
load	676s
index	0s
query 0	10s	29s
query 1	10s	10s
query 2	131s	197s
query 3	88s	89s
query 4	98s	224s

Case 1 - First Iteration

Discard the index on fact0.d0key and create one on fact0.(d0key, fval)

Test	Run 1	Run 2
load	655s
index	0s
query 0	2s	2s
query 1	2s	2s
query 2	133s	228s
query 3	89s	89s
query 4	22s	22s

This is a general improvement for everything except query2. See the plan output in the appendices. We are seeing index only access for the first 2 and last query.

Case 2 - Second Iteration

Create index on dim0.(dyr, dmth) as well as fact0.(d0key, fval)

Test	Run 1	Run 2
load	655s
index	0s
query 0	2s	2s
query 1	1s	1s
query 2	133s	231s
query 3	90s	90s
query 4	22s	22s

There is maybe a slight improvement. See the plan output in the appendices.

Case 3 - Third Iteration

Create index on fact0.(d0key, fval, d1key)

Test	Run 1	Run 2
load	661s
index	0s
query 0	2s	2s
query 1	2s	1s
query 2	46s	46s
query 3	133s	224s
query 4	23s	23s

This improves the results for query 2 - at the expense of query3. This is an overall improvement however. See the plan output in the appendices.

Using a similar extension - indexing fact0.(d0key, fval, d1key, d2key) does not improve matters any furthur. After looking at the access plans for Firebird performing these same queries, an alternative index configuration suggested itself (now there's database engine cross polonization for you...)

Case 4 - Fourth Iteration

Drop all default fact0 indexes. Create index on fact0.(d0key, fval, d1key) and another on fact0(d0key, fval, d2key, d1key)

Test	Run 1	Run 2
load	580s
index	0s
query 0	3s	3s
query 1	1s	1s
query 2	46s	46s
query 3	46s	47s
query 4	23s	23s

Appendicies

Schema And Queries

See Three Open Source Databases for these.

Query Plans For Case 0 - Default Indexes

+---------+
| Query 0 |
+---------+
| Query 0 |
+---------+
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key    | NULL        | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key   | fact0_d0key | 4       | benchw.d0.d0key |  1000 |                                              |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 1 |
+---------+
| Query 1 |
+---------+
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key    | NULL        | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key   | fact0_d0key | 4       | benchw.d0.d0key |  1000 |                                              |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 2 |
+---------+
| Query 2 |
+---------+
+----+-------------+-------+--------+-------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys           | key         | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+-------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key              | NULL        | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key,fact0_d1key | fact0_d0key | 4       | benchw.d0.d0key |  1000 |                                              |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key              | dim1_d1key  | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+-------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 3 |
+---------+
| Query 3 |
+---------+
+----+-------------+-------+--------+-------------------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                       | key         | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+-------------------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key                          | NULL        | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key,fact0_d1key,fact0_d2key | fact0_d0key | 4       | benchw.d0.d0key |  1000 |                                              |
|  1 | SIMPLE      | d2    | eq_ref | dim2_d2key                          | dim2_d2key  | 4       | benchw.f.d2key  |     1 | Using where                                  |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                          | dim1_d1key  | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+-------------------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 4 |
+---------+
| Query 4 |
+---------+
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key    | NULL        | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key   | fact0_d0key | 4       | benchw.d0.d0key |  1000 |                                              |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+

Query Plans For Case 1

+---------+
| Query 0 |
+---------+
| Query 0 |
+---------+
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key       | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 1 |
+---------+
| Query 1 |
+---------+
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key       | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 2 |
+---------+
| Query 2 |
+---------+
+----+-------------+-------+--------+------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key                   | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key_fval,fact0_d1key | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 |                                              |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                   | dim1_d1key       | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 3 |
+---------+
| Query 3 |
+---------+
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                            | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key                               | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key_fval,fact0_d1key,fact0_d2key | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 |                                              |
|  1 | SIMPLE      | d2    | eq_ref | dim2_d2key                               | dim2_d2key       | 4       | benchw.f.d2key  |     1 | Using where                                  |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                               | dim1_d1key       | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 4 |
+---------+
| Query 4 |
+---------+
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key       | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+

Query Plans For Case 2

 
+---------+
| Query 0 |
+---------+
| Query 0 |
+---------+
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key       | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+------------------+------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 1 |
+---------+
| Query 1 |
+---------+
+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys            | key              | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | d0    | ref  | dim0_d0key,dim0_dyr_dmth | dim0_dyr_dmth    | 4       | const           |  295 | Using where |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval         | fact0_d0key_fval | 4       | benchw.d0.d0key | 1000 | Using index |
+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+------+-------------+
+---------+
| Query 2 |
+---------+
| Query 2 |
+---------+
+----+-------------+-------+--------+------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key,dim0_dyr_dmth     | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key_fval,fact0_d1key | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 |                                              |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                   | dim1_d1key       | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 3 |
+---------+
| Query 3 |
+---------+
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                            | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key,dim0_dyr_dmth                 | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key_fval,fact0_d1key,fact0_d2key | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 |                                              |
|  1 | SIMPLE      | d2    | eq_ref | dim2_d2key                               | dim2_d2key       | 4       | benchw.f.d2key  |     1 | Using where                                  |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                               | dim1_d1key       | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 4 |
+---------+
| Query 4 |
+---------+
+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys            | key              | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key,dim0_dyr_dmth | NULL             | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval         | fact0_d0key_fval | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+-------+----------------------------------------------+

Query Plans For Case 3

+---------+
| Query 0 |
+---------+
| Query 0 |
+---------+
+----+-------------+-------+------+------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys          | key                    | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key             | NULL                   | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval_d1key | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 1 |
+---------+
| Query 1 |
+---------+
+----+-------------+-------+------+--------------------------+------------------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys            | key                    | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+--------------------------+------------------------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | d0    | ref  | dim0_d0key,dim0_dyr_dmth | dim0_dyr_dmth          | 4       | const           |  295 | Using where |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval_d1key   | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key | 1000 | Using index |
+----+-------------+-------+------+--------------------------+------------------------+---------+-----------------+------+-------------+
+---------+
| Query 2 |
+---------+
| Query 2 |
+---------+
+----+-------------+-------+--------+------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                      | key                    | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key,dim0_dyr_dmth           | NULL                   | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key_fval_d1key,fact0_d1key | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key |  1000 | Using index                                  |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                         | dim1_d1key             | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 3 |
+---------+
| Query 3 |
+---------+
+----+-------------+-------+--------+------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                  | key                    | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key,dim0_dyr_dmth                       | NULL                   | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key_fval_d1key,fact0_d1key,fact0_d2key | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key |  1000 |                                              |
|  1 | SIMPLE      | d2    | eq_ref | dim2_d2key                                     | dim2_d2key             | 4       | benchw.f.d2key  |     1 | Using where                                  |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                                     | dim1_d1key             | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 4 |
+---------+
| Query 4 |
+---------+
+----+-------------+-------+------+--------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys            | key                    | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+--------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key,dim0_dyr_dmth | NULL                   | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval_d1key   | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+--------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+

Query Plans For Case 4

+---------+
| Query 0 |
+---------+
| Query 0 |
+---------+
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys                                       | key                    | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key                                          | NULL                   | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval_d1key,fact0_d0key_fval_d2key_d1key | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 1 |
+---------+
| Query 1 |
+---------+
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys                                       | key                    | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | d0    | ref  | dim0_d0key,dim0_dyr_dmth                            | dim0_dyr_dmth          | 4       | const           |  295 | Using where |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval_d1key,fact0_d0key_fval_d2key_d1key | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key | 1000 | Using index |
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+------+-------------+
+---------+
| Query 2 |
+---------+
| Query 2 |
+---------+
+----+-------------+-------+--------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                       | key                    | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key,dim0_dyr_dmth                            | NULL                   | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key_fval_d1key,fact0_d0key_fval_d2key_d1key | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key |  1000 | Using index                                  |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                                          | dim1_d1key             | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 3 |
+---------+
| Query 3 |
+---------+
+----+-------------+-------+--------+-----------------------------------------------------+------------------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                       | key                          | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------------+------------------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL    | dim0_d0key,dim0_dyr_dmth                            | NULL                         | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref    | fact0_d0key_fval_d1key,fact0_d0key_fval_d2key_d1key | fact0_d0key_fval_d2key_d1key | 4       | benchw.d0.d0key |  1000 | Using index                                  |
|  1 | SIMPLE      | d2    | eq_ref | dim2_d2key                                          | dim2_d2key                   | 4       | benchw.f.d2key  |     1 | Using where                                  |
|  1 | SIMPLE      | d1    | eq_ref | dim1_d1key                                          | dim1_d1key                   | 4       | benchw.f.d1key  |     1 | Using where                                  |
+----+-------------+-------+--------+-----------------------------------------------------+------------------------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 4 |
+---------+
| Query 4 |
+---------+
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys                                       | key                    | key_len | ref             | rows  | Extra                                        |
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | d0    | ALL  | dim0_d0key,dim0_dyr_dmth                            | NULL                   | NULL    | NULL            | 10000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | ref  | fact0_d0key_fval_d1key,fact0_d0key_fval_d2key_d1key | fact0_d0key_fval_d1key | 4       | benchw.d0.d0key |  1000 | Using index                                  |
+----+-------------+-------+------+-----------------------------------------------------+------------------------+---------+-----------------+-------+----------------------------------------------+