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

Question

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')

?

I would also be interested in knowing: what is the relative performance of a query using `REGEXP` for the same purpose? — JYelton, Jul 09 '10 at 15:18
What database? They will have entirely different performance characteristics in different databases. — Oded, Jul 09 '10 at 15:19
@Oded: MS-SQL Server and MySQL are the two I use most. For the purposes of the question, I assume SQL Server is of most interest. — JYelton, Jul 09 '10 at 15:22
@dotnetguts can you provide me a link where i can read about CONTAINS proper usage...so far all i know is A CONTAINS B means A is superset of B. — abhimanyuaryan, Mar 10 '15 at 00:50

score 45 · Accepted Answer · edited Feb 19 '21 at 22:04

45

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. I don't know 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.

edited Feb 19 '21 at 22:04

peterh

11,875
18
85
108

answered Jul 09 '10 at 15:22

OMG Ponies

325,700
82
523
502

4

In MS SQL Server, a full text index is required in order to use CONTAINS. – Dave Carlile Jul 09 '10 at 15:26
1

Is it going to be faster than full text search engines.. like Lucene / Solr? Especially in case of millions of records. – Krunal Jun 28 '13 at 09:33

score 6 · Answer 2 · answered Jul 09 '10 at 15:20

6

For a typical database, the CONTAINS search can be much faster assuming the appropriate full text search index is built on the field being searched. The evaluation of the LIKE operator generally doesn't use an index and thus must read all the data.

answered Jul 09 '10 at 15:20

Mark Wilkins

40,729
5
57
110

score -2 · Answer 3 · answered Sep 18 '15 at 10:25

-2

Like search on Table it self, will kill the performance. Better to apply like search on CTE.

answered Sep 18 '15 at 10:25

Agrawars

37
2

try below url http://stackoverflow.com/questions/1569002/how-can-i-optimize-refactor-a-tsql-like-clause/32329298#32329298 – Agrawars Sep 18 '15 at 10:26

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

3 Answers3

Linked