When should you use full-text indexing?

sql sql-server tsql indexing full-text-search
Esteban Araya picture Esteban Araya · Sep 12, 2008 · Viewed 49.6k times · Source

We have a whole bunch of queries that "search" for clients, customers, etc. You can search by first name, 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? We're using SQL Server 2005.

Answer

Prestaul picture Prestaul · Sep 12, 2008

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

Related questions

Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More
sql primary key and index

Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed? …

Read More
How important is the order of columns in indexes?

I've heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I'm saying correct? If so, am …

Read More