February 15, 2016 at 6:17 pm
The biggest gain that full text indexing gives you is synonyms and similar functionality. As others have said, the LIKE match is the most flexible, but that leading wildcard characters makes it so you have to scan the whole column and that's where your performance hit comes in. Yes, you can use the technique that Orlando suggested, but that works best in cases where you have a bunch of NULLs that you can exclude right from the get-go; you still have to scan the rest. If you have a mostly-populated column, it isn't going to buy you much.
The best case is to eliminate the need for that leading wildcard, which may not be possible. Depending on your exact situation, there is the possibility of creating a table and populating it with the leading strings of each row, but that consumes an awful lot of rows. I'd exhaust other possibilities before trying it.
February 16, 2016 at 1:01 am
Thanks for all the reply's
I will try to make the like faster.
the columns are not nullable, so the full scan needs to be done.
I will mark Jacob's answer as positive.
February 16, 2016 at 2:21 am
Please read the article I linked to. While a scan of the data is inevitable with like%% the technique in the article still improves performance even with 0% nulls.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply