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
