List:General Discussion« Previous MessageNext Message »
From:Marko Knezevic Date:November 22 2005 2:27pm
Subject:Re: Tips for better performance
View as plain text  
I also have following situation:


SELECT count(*) FROM  accounts LEFT JOIN users ON 
accounts.assigned_user_id=users.id where ((accounts.phone_office like '01 
389437%' OR accounts.phone_alternate like '01 389437%' OR accounts.phone_fax 
like '01 389437%')) AND  accounts.deleted=0



Explain says it's ok with:

id,        select_type,      table,    type,     possible_keys,  key, 
key_len,           ref,       rows,    Extra

1,         SIMPLE,           accounts, 
index,phone_fax,phone_office,idx_phone_alt,idx_asofalfade_tmp,116,NULL,888466,Using 
where; Using index

1,         SIMPLE,           users, 
eq_ref,PRIMARY,PRIMARY,36,sugarcrm.accounts.assigned_user_id,1,Using indeks



And it takes about 1.5 secs on about 1.000.000 records..



BUT



explain SELECT users.user_name assigned_user_name,  accounts.* FROM 
accounts LEFT JOIN users ON accounts.assigned_user_id=users.id where 
((accounts.phone_office like '01 389437%' OR accounts.phone_alternate like 
'01 389437%' OR accounts.phone_fax like '01 389437%')) AND 
accounts.deleted=0  ORDER BY phone_office asc LIMIT 0,20



says:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,SIMPLE,accounts,index,phone_fax,phone_office,idx_phone_alt,phone_office,26,NULL,888466,Using

where

1,SIMPLE,users,eq_ref,PRIMARY,PRIMARY,36,sugarcrm.accounts.assigned_user_id,1,



And takes more than 8 seconds.. (it can also be ORDERED BY phone_office like 
this time, or by name, address or something..)

Thread
Tips for better performanceMarko Knezevic22 Nov
  • Re: Tips for better performanceDuncan Hill22 Nov
  • Re: Tips for better performanceScott Haneda22 Nov
  • Re: Tips for better performanceMarko Knezevic22 Nov
    • Re: Tips for better performanceDuncan Hill22 Nov
  • Re: Tips for better performanceRonan Lucio22 Nov
  • Re: Tips for better performanceMartijn Tonies22 Nov
  • Re: Tips for better performanceRonan Lucio22 Nov
  • Re: Tips for better performanceMartijn Tonies22 Nov
  • Re: Tips for better performanceMarko Knezevic22 Nov
  • Re: Tips for better performanceMarko Knezevic22 Nov
  • Re: Tips for better performanceDan Baker22 Nov
  • Re: Tips for better performanceRonan Lucio23 Nov
  • Re: Tips for better performanceMarko Knezevic23 Nov
  • Re: Tips for better performanceMarko Knezevic23 Nov
  • Re: Tips for better performanceRonan Lucio23 Nov