List:General Discussion« Previous MessageNext Message »
From:Simon Kimber Date:February 1 2009 9:17pm
Subject:Trying to work out why a join query is so slow
View as plain text  
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

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