List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:August 20 2009 2:43pm
Subject:Re: Simple join very slow
View as plain text  
MySQL can only use one index at a time. Your OR's makes it unable to use any
of the indexes.

You could try a UNION:

SELECT * FROM users
WHERE users.username='user1234'
UNION
SELECT * FROM users
INNER JOIN sites ON users.id=sites.userid
WHERE users.email='test@stripped'
UNION
 SELECT * FROM users
 INNER JOIN sites ON users.id=sites.userid
WHERE sites.email='test@stripped'
;

Indexs should be on:
users.username, users.id
users.email
sites.email, sites.userid



On Thu, Aug 20, 2009 at 7:25 AM, Simon Kimber <simon@stripped>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 <http://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
>



-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

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