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

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