From: Rick James
Date: April 20 2009 4:44pm
Subject: RE: one join filter is eliminated from the execution plan
List-Archive: http://lists.mysql.com/internals/36563
Message-Id: <82624B02DA0A3C4691069A83007F651D036571E1@SNV-EXVS08.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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

=20
Rick James
MySQL Geeks - Consulting & Review

=20

> -----Original Message-----
> From: Igor.Babaev@stripped [mailto:Igor.Babaev@stripped]=20
> 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
>=20
> I'm sorry, but don't see neither c_nationkey=3D4 nor=20
> (n_nationkey=3Dc_nationkey) in the query below.
>=20
> What query do you actually execute?
>=20
> Regards,
> Igor.
>=20
> Zhixuan Zhu wrote:
> > Given the following query, It makes sense that MySQL=20
> optimizer adds one filter "c_nationkey=3D4" to the execution=20
> plan. But I don't understand why the join between nation and=20
> customer (n_nationkey=3Dc_nationkey) gets dropped. It will end=20
> up with a Cartesian product between nation and the rest of=20
> join result, which does not look optimal. Is this a bug of=20
> MySQL optimizer?
> >=20
> > mysql> explain select l_partkey from customer, orders,=20
> lineitem, nation, part where o_totalprice > 450000.0 and=20
> n_regionkey =3D 4 and c_custkey =3D o_custkey and o_orderkey =3D=20
> l_orderkey and n_regionkey =3D c_nationkey and l_partkey =3D 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)
> >=20
> > Thanks very much,
> > Zhixuan
> >=20
> >=20
> > --
> > MySQL Internals Mailing List
> > For list archives: http://lists.mysql.com/internals
> > To unsubscribe:   =20
> http://lists.mysql.com/internals?unsub=3Digor@stripped
> >=20
>=20
> --=20
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals