List:Internals« Previous MessageNext Message »
From:Rick James Date:April 20 2009 4:44pm
Subject:RE: one join filter is eliminated from the execution plan
View as plain text  
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
Thread
one join filter is eliminated from the execution planZhixuan Zhu17 Apr
  • Re: one join filter is eliminated from the execution planIgor Babaev18 Apr
    • RE: one join filter is eliminated from the execution planRick James20 Apr