explain + straight join oddity

[Stuart Maclean]

Using mysql 5.0.15, myiasm engine. REHL 3.

Have a view defined as a select on a 15 table join (actually via nested views but I think that is irrelevant). Run explain on the view and get this

mysql> explain select * from view;

+----+-------------+--------+--------+----------------------------------------+---------+---------+---------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+----------------------------------------+---------+---------+---------------+------+-------+
| 1 | PRIMARY | C | ALL | a,indexCmpIn,cmps_index_b,cmps_index_a | NULL | NULL | NULL | 684 | |
| 1 | PRIMARY | P1 | eq_ref | PRIMARY,what | PRIMARY | 8 | foo.C.a | 1 | |
| 1 | PRIMARY | whatA | eq_ref | PRIMARY | PRIMARY | 2 | foo.P1.what | 1 | |
| 1 | PRIMARY | gsA | eq_ref | PRIMARY,yx | PRIMARY | 4 | foo.P1.where_ | 1 | |
| 1 | PRIMARY | yxA | eq_ref | PRIMARY | PRIMARY | 2 | foo.gsA.yx | 1 | |
| 1 | PRIMARY | zA | eq_ref | PRIMARY | PRIMARY | 2 | foo.gsA.z | 1 | |
| 1 | PRIMARY | whoA | eq_ref | PRIMARY | PRIMARY | 2 | foo.P1.who | 1 | |
| 1 | PRIMARY | gridsA | eq_ref | PRIMARY | PRIMARY | 2 | foo.yxA.grid | 1 | |
| 1 | PRIMARY | P2 | eq_ref | PRIMARY,what | PRIMARY | 8 | foo.C.b | 1 | |
| 1 | PRIMARY | whatB | eq_ref | PRIMARY | PRIMARY | 2 | foo.P2.what | 1 | |
| 1 | PRIMARY | whoB | eq_ref | PRIMARY | PRIMARY | 2 | foo.P2.who | 1 | |
| 1 | PRIMARY | gsB | eq_ref | PRIMARY,yx | PRIMARY | 4 | foo.P2.where_ | 1 | |
| 1 | PRIMARY | zB | eq_ref | PRIMARY | PRIMARY | 2 | foo.gsB.z | 1 | |
| 1 | PRIMARY | yxB | eq_ref | PRIMARY | PRIMARY | 2 | foo.gsB.yx | 1 | |
| 1 | PRIMARY | gridsB | eq_ref | PRIMARY | PRIMARY | 2 | foo.yxB.grid | 1 | |
+----+-------------+--------+--------+----------------------------------------+---------+---------+---------------+------+-------+
15 rows in set (22.69 sec)

Thus a 684 row result set takes 22 secs. Apparentlty a nasty case for the optimizer. The table with many rows is scanned first, so the amount of backtracking in the 'single sweep multi join' alogorithm and subsequent primary key look ups is HUGE.

Now, I define a second view whose table ordering in the 'from' clause mimics that of the table scan order in the above explain, and then do a STRAIGHT JOIN on that view:

mysql> explain select straight_join * from cmpv50;
+----+-------------+--------+--------+----------------------------------------+---------+---------+---------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+----------------------------------------+---------+---------+---------------+------+-------+
| 1 | PRIMARY | C | ALL | a,indexCmpIn,cmps_index_b,cmps_index_a | NULL | NULL | NULL | 684 | |
| 1 | PRIMARY | P1 | eq_ref | PRIMARY,what | PRIMARY | 8 | foo.C.a | 1 | |
| 1 | PRIMARY | whatA | eq_ref | PRIMARY | PRIMARY | 2 | foo.P1.what | 1 | |
| 1 | PRIMARY | gsA | eq_ref | PRIMARY,yx | PRIMARY | 4 | foo.P1.where_ | 1 | |
| 1 | PRIMARY | yxA | eq_ref | PRIMARY | PRIMARY | 2 | foo.gsA.yx | 1 | |
| 1 | PRIMARY | zA | eq_ref | PRIMARY | PRIMARY | 2 | foo.gsA.z | 1 | |
| 1 | PRIMARY | whoA | eq_ref | PRIMARY | PRIMARY | 2 | foo.P1.who | 1 | |
| 1 | PRIMARY | gridsA | eq_ref | PRIMARY | PRIMARY | 2 | foo.yxA.grid | 1 | |
| 1 | PRIMARY | P2 | eq_ref | PRIMARY,what | PRIMARY | 8 | foo.C.b | 1 | |
| 1 | PRIMARY | whatB | eq_ref | PRIMARY | PRIMARY | 2 | foo.P2.what | 1 | |
| 1 | PRIMARY | whoB | eq_ref | PRIMARY | PRIMARY | 2 | foo.P2.who | 1 | |
| 1 | PRIMARY | gsB | eq_ref | PRIMARY,yx | PRIMARY | 4 | foo.P2.where_ | 1 | |
| 1 | PRIMARY | zB | eq_ref | PRIMARY | PRIMARY | 2 | foo.gsB.z | 1 | |
| 1 | PRIMARY | yxB | eq_ref | PRIMARY | PRIMARY | 2 | foo.gsB.yx | 1 | |
| 1 | PRIMARY | gridsB | eq_ref | PRIMARY | PRIMARY | 2 | foo.yxB.grid | 1 | |
+----+-------------+--------+--------+----------------------------------------+---------+---------+---------------+------+-------+
15 rows in set (0.00 sec)

Same explain order as before, but now takes 0 seconds. Quite a speed up ;)

Now, given that a straight join is quicker here, how does one derive the optimal (if such a thing exists??) ordering of table names when intending to do straight joins. Must have so many variables, row counts etc??

Just thought this might be of interest. Is there a simple explanation ???

Stuart