From: Martin Gainty
Date: February 1 2009 11:22pm
Subject: RE: Trying to work out why a join query is so slow
List-Archive: http://lists.mysql.com/mysql/216122
Message-Id: <BLU142-W44B36D3ADE2490B424008DAEC40@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_f4e51136-3e8d-472b-8a2c-b8694686c560_"

--_f4e51136-3e8d-472b-8a2c-b8694686c560_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


UNION all does a cartesian join ..maxiumum number of results will be delive=
red
SELECT *
 FROM sites
 INNER JOIN sites.userid =3D users.ID=3B
-- INNER JOIN users ON sites.userid =3D users.ID

--ALSO put (hopefully UNIQUE indexes) on sites.userid and users.id
Martin=20
______________________________________________=20
Disclaimer and confidentiality note=20
Everything in this e-mail and any attachments relates to the official busin=
ess of Sender. This transmission is of a confidential nature and Sender doe=
s not endorse distribution to any party other than intended recipient. Send=
er does not necessarily endorse content contained within this transmission.=
=20




> Date: Sun=2C 1 Feb 2009 17:23:10 -0500
> Subject: Re: Trying to work out why a join query is so slow
> From: ysndar@stripped
> To: simon@stripped
> CC: mysql@stripped
>=20
> My guess is that the OR is searching the whole table for each element of =
the
> other table. It compounds the select statement.
> You may try a Union.Im new to Mysql so im not sure it will work=2C but yo=
u
> might try it out.
>=20
> SELECT *
> FROM sites
> INNER JOIN users ON sites.userid =3D users.ID
> WHERE sites.email =3D 'person@stripped'
> UNION
> SELECT *
> FROM sites
> INNER JOIN users ON sites.userid =3D users.ID
> WHERE userid.email =3D 'person@stripped'
>=20
> On Sun=2C Feb 1=2C 2009 at 4:17 PM=2C Simon Kimber <simon@stripped=
td.uk>wrote:
>=20
> > Hi Everyone=2C
> >
> > I'm trying to run a very simple query on two joined tables but it's tak=
ing
> > a long time to run.
> >
> > I have two tables=2C users and sites=2C both of which have an email add=
ress
> > field that I'm querying.
> >
> > here's my query:
> >
> > SELECT *
> > FROM sites
> > INNER JOIN users ON sites.userid =3D users.ID
> > WHERE sites.email =3D 'person@stripped'
> > OR users.email =3D 'person@stripped'
> >
> > both tables contain over 100k rows. users.ID is a primary key=2C and
> > sites.userid=2C sites.email and users.email all have indices.
> >
> > The query above is taking over 3.3 seconds to run=2C but if i only use =
one of
> > the where clauses=2C ie. I only search on users.email or I only search =
on
> > sites.email=2C the query takes around 0.002 seconds to run.
> >
> > As soon as I try and run the query with BOTH where clauses it takes
> > exponentially longer!
> >
> > Can anyone suggest what might be the problem or how I could rewrite the
> > query to significantly speed it up?
> >
> > Thanks!
> >
> > Simon
> >
>=20
>=20
>=20
> --=20
> Sangeetha Damodar
> www.vlsibank.com

_________________________________________________________________
Windows Live=99 Hotmail=AE:=85more than just e-mail.=20
http://windowslive.com/explore?ocid=3DTXT_TAGLM_WL_t2_hm_justgotbetter_expl=
ore_012009=

--_f4e51136-3e8d-472b-8a2c-b8694686c560_--