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: [email protected] > To: [email protected] > CC: [email protected] > > 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 = '[email protected]' > UNION > SELECT * > FROM sites > INNER JOIN users ON sites.userid = users.ID > WHERE userid.email = '[email protected]' > > On Sun, Feb 1, 2009 at 4:17 PM, Simon Kimber > <[email protected]>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 = '[email protected]' > > OR users.email = '[email protected]' > > > > 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
