When should you use full-text indexing?

Question

We have a whole bunch fo queries that "search" for clients, customers, etc. You can search by firstname, email, etc. We're using LIKE statements in the following manner:

select * from customer where fname like '%someName%'

Does full-text indexing help in the scenario?

Edit:
I failed to mention we're using MSSQL 2005.

Answer 1

It will depend upon your DBMS. I believe that most systems will not take advantage of the full-text index unless you use the full-text functions. (e.g. MATCH/AGAINST in mySQL or FREETEXT/CONTAINS in MS SQL)

Here is a good article on when, why, and how to use full-text indexing in SQL Server: Understanding SQL Server Full-Text Indexing

Answer 2

To answer the question specifically for MSSQL, full-text indexing will NOT help in your scenario.

In order to improve that query you could do one of the following:

1. Configure a full-text catalog on the column and use the CONTAINS() function.

2. If you were primarily searching with a prefix (i.e. matching from the start of the name), you could change the predicate to the following and create an index over the column.

   where fname like 'prefix%'

(1) is probably overkill for this, unless the performance of the query is a big problem.

Answer 3

FTS can help in this scenario, the question is whether it is worth it or not.

To begin with, let's look at why LIKE may not be the most effective search. When you use LIKE, especially when you are searching with a % at the beginning of your comparison, SQL Server needs to perform both a table scan of every single row and a byte byte by byte check of the column you are checking.

FTS has some better algorithms for matching data as does some better statistics on variations of names. Therefore FTS can provide better performance for matching Smith, Smythe, Smithers, etc when you look for Smith.

It is, however, a bit more complex to use FTS, as you'll need to master CONTAINS vs FREETEXT and the arcane format of the search. However, if you want to do a search where either FName or LName match, you can do that with one statement instead of an OR.

To determine if FTS is going to be effective, determine how much data you have. I use FTS on a database of several hundred million rows and that's a real benefit over searching with LIKE, but I don't use it on every table.

If your table size is more reasonable, less than a few million, you can get similar speed by creating an index for each column that you're going to be searching on and SQL Server should perform an index scan rather than a table scan.