I'm sorry, but don't see neither c_nationkey=4 nor
(n_nationkey=c_nationkey) in the query below.
What query do you actually execute?
Regards,
Igor.
Zhixuan Zhu wrote:
> Given the following query, It makes sense that MySQL optimizer adds one filter
> "c_nationkey=4" to the execution plan. But I don't understand why the join between nation
> and customer (n_nationkey=c_nationkey) gets dropped. It will end up with a Cartesian
> product between nation and the rest of join result, which does not look optimal. Is this a
> bug of MySQL optimizer?
>
> mysql> explain select l_partkey from customer, orders, lineitem, nation, part
> where o_totalprice > 450000.0 and n_regionkey = 4 and c_custkey = o_custkey and
> o_orderkey = l_orderkey and n_regionkey = c_nationkey and l_partkey = p_partkey \G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: nation
> type: ALL
> possible_keys: NULL
> key: NULL
> key_len: NULL
> ref: NULL
> rows: 25
> Extra: Using where
> *************************** 2. row ***************************
> id: 1
> select_type: SIMPLE
> table: customer
> type: ref
> possible_keys: i_c_custkey,i_c_nationkey
> key: i_c_nationkey
> key_len: 5
> ref: const
> rows: 5917
> Extra: Using where
> *************************** 3. row ***************************
> id: 1
> select_type: SIMPLE
> table: orders
> type: ref
> possible_keys: i_o_orderkey,i_o_custkey
> key: i_o_custkey
> key_len: 5
> ref: qrefd01m.customer.c_custkey
> rows: 15
> Extra: Using where
> *************************** 4. row ***************************
> id: 1
> select_type: SIMPLE
> table: lineitem
> type: ref
> possible_keys: i_l_orderkey,i_l_partkey
> key: i_l_orderkey
> key_len: 5
> ref: qrefd01m.orders.o_orderkey
> rows: 4
> Extra: Using where
> *************************** 5. row ***************************
> id: 1
> select_type: SIMPLE
> table: part
> type: ref
> possible_keys: i_p_partkey
> key: i_p_partkey
> key_len: 5
> ref: qrefd01m.lineitem.l_partkey
> rows: 1
> Extra: Using where; Using index
> 5 rows in set (0.02 sec)
>
> Thanks very much,
> Zhixuan
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=1
>
