Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
383 views
in Technique[技术] by (71.8m points)

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

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

?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...