List:General Discussion« Previous MessageNext Message »
From:Simon Kimber Date:August 20 2009 12:25pm
Subject:Simple join very slow
View as plain text  
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