List:General Discussion« Previous MessageNext Message »
From:mos Date:August 20 2009 3:28pm
Subject:Re: Simple join very slow
View as plain text  
You didn't say what version of MySQL you're using or if you're using MyISAM 
tables (assumed).
Since you are using OR's you may find it faster to use Union on 3 select 
statements.

It looks something like this:

SELECT * FROM users
JOIN sites ON users.ID = sites.userid
WHERE users.username = 'user1234'
union
SELECT * FROM users
JOIN sites ON users.ID = sites.userid
WHERE users.email = 'test@stripped'
union
SELECT * FROM users
JOIN sites ON users.ID = sites.userid
WHERE sites.email = 'test@stripped' <mailto:'ccfcfe@stripped'>

This way each Select statement will use whatever index they like, 
independent of the other Select statements.

Mike


At 07:25 AM 8/20/2009, you wrote:
>Hi Everyone,
>
>Can anyone suggest why the following query is taking upwards of 5
>seconds to run?
>
>SELECT * FROM users
>JOIN sites ON users.ID = sites.userid
>WHERE users.username = 'user1234'
>OR users.email = 'test@stripped'
>OR sites.email = 'test@stripped' <mailto:'ccfcfe@stripped'>
>
>The users table has an index on the username field and another on the
>email field.
>
>The sites table has an index on the email field.
>
>Both tables contain around 200k rows.
>
>An explain of the query shows that no indexes are being used:
>
>id      select_type     table   type    possible_keys   key     key_len
>ref     rows    Extra
>1       SIMPLE  users   ALL     PRIMARY,username,email  NULL    NULL
>NULL    155424
>1       SIMPLE  sites   ref     userid,email    userid  4
>dyos.users.ID   1       Using where
>
>Can anyone tell me how to make it use the available indexes?  Or do I
>need to add some sort of multi-column index?  I'm guessing not as I'm
>doing OR's
>
>Thanks
>
>Simon


Thread
Simple join very slowSimon Kimber20 Aug
  • Re: Simple join very slowJohnny Withers20 Aug
    • Re: Simple join very slowPerrin Harkins20 Aug
Re: Simple join very slowmos20 Aug