Please also show the following so we can get a better feel for the
situation:
SHOW TABLE STATUS\G -- need to see sizes of tables
SHOW CREATE TABLE tbl\G -- need to see indexes, table type, etc
Rick James
MySQL Geeks - Consulting & Review
> -----Original Message-----
> From: Igor.Babaev@stripped [mailto:Igor.Babaev@stripped]
> Sent: Friday, April 17, 2009 3:52 PM
> To: Zhixuan Zhu
> Cc: internals@stripped
> Subject: Re: one join filter is eliminated from the execution plan
>
> 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
> >
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
