MySQL Forums
Forum List  »  Optimizer & Parser

explain + straight join oddity
Posted by: Stuart Maclean
Date: December 20, 2005 08:06PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
explain + straight join oddity
8875
December 20, 2005 08:06PM
2697
December 21, 2005 10:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.