This is going to return duplicate rows if there are results that match both conditions. One of the queries needs a condition to exclude the results that'll be sent in the other query.
You can do it this way, and in some cases it's faster. But, what I think we should really be asking is: 1) what is the output of EXPLAIN SELECT ..... \G for the original query? Notice the \G terminator -- not a semicolon. 2) what is the output of SHOW CREATE TABLE....\G for each table? On Sun, Feb 1, 2009 at 5:23 PM, Sangeetha <ysn...@gmail.com> wrote: > 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 = 'per...@domain.com' > UNION > SELECT * > FROM sites > INNER JOIN users ON sites.userid = users.ID > WHERE userid.email = 'per...@domain.com' > > On Sun, Feb 1, 2009 at 4:17 PM, Simon Kimber > <si...@internetstuff.ltd.uk>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 = 'per...@domain.com' >> OR users.email = 'per...@domain.com' >> >> 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 > -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
