Performance of like '%Query%' vs full text search CONTAINS query

sql database performance full-text-search sql-like
dotnetguts picture dotnetguts · Jul 9, 2010 · Viewed 69k times · Source

I have a situation where I would like to search a single word.

For that scenario, which query would be good from a performance point of view?

Select Col1, Col2 from Table Where Col1 Like '%Search%'

or 

Select Col1, Col2 from Table Where Col1 CONTAINS(Col1,'Search')

?

Answer

OMG Ponies picture OMG Ponies · Jul 9, 2010

Full Text Searching (using the CONTAINS) will be faster/more efficient than using LIKE with wildcarding. Full Text Searching (FTS) includes the ability to define Full Text Indexes, which FTS can use. Dunno why you wouldn't define a FTS index if you intended to use the functionality...

LIKE with wildcarding on the left side (IE: LIKE '%Search') can not use an index (assuming one exists for the column), guaranteeing a table scan. I haven't tested & compared, but regex has the same pitfall. To clarify, LIKE '%Search' and LIKE '%Search%' can not use an index; LIKE 'Search%' can use an index.

Related questions

How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How do I index a database column.

Read More
How to check which locks are held on a table

How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005

Read More
SQL, Postgres OIDs, What are they and why are they useful?

I am looking at some PostgreSQL table creation and I stumbled upon this: CREATE TABLE ( ... ) WITH ( OIDS = FALSE ); I read the documentation provided by postgres and I know the concept of object identifier from OOP but still I do not …

Read More