LIKE query Using index

[Anon]

I have following query in my php code

SELECT count(t1.cat) as count,t2.cat as cat, t2.id as id, t1.mcat as mid FROM t2,t1 WHERE t2.name LIKE '%$Name%' and t1.id = t2.cat and t2.phone LIKE '$AreaCode%' group by t2.subcat order by t2.id,t1.cat

Now I know this query will not use index on t2.name column since i am using wild card for '%$Name%'. But I need to use wild card cause I need to find a string that contains search word and not begins with search word.

Is there any other work around for this?

Thanks
PP

[ljoncz]

hi,

using LIKE '%$NAME%' won't give you desired results, because it will return strings starting with $NAME, or even equal to $NAME as well. What you need is something like this:
... t2.name NOT LIKE '$NAME%' AND t2.name LIKE '%$NAME%'...

Although it is extending query size it can in fact improve its performance, because indexes can be used in the first condition, and I belive MySQL optimiser is smart enough to take advantage of this fact and check the first condition in the first place.

cheers
Leszek