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..)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
