I had this question after viewing Is this correct: first % in "Like" ignores indexes?
I asked the question above the other day and I understand it but hoping for a miracle, workaround something :)
A prefix of % does not make SQL "ignore" the index, it means that SQL can't do a seek on the index. It could still scan the index to satisfy the query
I think I came across a link that had a workaround and now I can't find it. I can't use FTS and can't have word% search.
Anyway to speed up a search using %word% by adding indexes? I've already broken down the stored proc and get chunks of data but it would be great if I could add an index to 5 columns in the "where" clause that I'm searching on.
If not doable, then it is what it is.