List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:February 1 2009 11:22pm
Subject:RE: Trying to work out why a join query is so slow
View as plain text  
UNION all does a cartesian join ..maxiumum number of results will be delivered
SELECT *
 FROM sites
 INNER JOIN sites.userid = users.ID;
-- INNER JOIN users ON sites.userid = users.ID

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




> Date: Sun, 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
> 
> 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, but you
> might try it out.
> 
> SELECT *
> FROM sites
> INNER JOIN users ON sites.userid = users.ID
> WHERE sites.email = 'person@stripped'
> UNION
> SELECT *
> FROM sites
> INNER JOIN users ON sites.userid = users.ID
> WHERE userid.email = 'person@stripped'
> 
> On Sun, Feb 1, 2009 at 4:17 PM, Simon Kimber
> <simon@stripped>wrote:
> 
> > Hi Everyone,
> >
> > I'm trying to run a very simple query on two joined tables but it's taking
> > a long time to run.
> >
> > I have two tables, users and sites, both of which have an email address
> > field that I'm querying.
> >
> > here's my query:
> >
> > SELECT *
> > FROM sites
> > INNER JOIN users ON sites.userid = users.ID
> > WHERE sites.email = 'person@stripped'
> > OR users.email = 'person@stripped'
> >
> > both tables contain over 100k rows. users.ID is a primary key, and
> > sites.userid, sites.email and users.email all have indices.
> >
> > The query above is taking over 3.3 seconds to run, but if i only use one of
> > the where clauses, ie. I only search on users.email or I only search on
> > sites.email, 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
> >
> 
> 
> 
> -- 
> Sangeetha Damodar
> www.vlsibank.com

_________________________________________________________________
Windows Live™ Hotmail®:…more than just e-mail. 
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_explore_012009
Thread
Trying to work out why a join query is so slowSimon Kimber1 Feb
  • Re: Trying to work out why a join query is so slowSangeetha1 Feb
    • RE: Trying to work out why a join query is so slowMartin Gainty2 Feb
    • Re: Trying to work out why a join query is so slowBaron Schwartz3 Feb