SQL server 2005-- using Fulltext index to replace 'Like' to improve the Performance

Like is famous by its slowness, however it can be supported by normal DB index you have been created.

select id
from my_table
where site_id = 121 and my_name like '%admin%'

we create and it is index site_id and my_name columns as its non_clustered index. the execution plan shows index seek followed.It takes more than 6 seconds to execute and its reads as high as 20,000

It takes 90% time on 'like', so Want to find a replacement for such a 'Like'. Anyway, i searched 'contains()' out, looks like cool!

Enable Fulltext index on your table:

EXEC [YOURDB].[dbo].[sp_fullext_database] @action = 'enable'

Right click that table -> Define a Fulltext index -> select column name to be indexed: here i am choosing 'my_name' column

update your SQL statement
select id
from my_table
where site_id = 121 and contains (my_name like '"*admin*"')

It is even 6 times faster for my test case.take less than 1 seconds. and its reads reduce to 150!! OMG, I do not know the principle behind it, but it rocks for this case anyway!