List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:February 3 2009 2:31am
Subject:Re: Trying to work out why a join query is so slow
View as plain text  
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 <ysndar@stripped> 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 = '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
>



-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
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