性能像“％Query％”对全文搜索CONTAINS查询 [英] 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')

？

推荐答案

全文搜索CONTAINS）将比使用通配符的LIKE更快/更有效。全文本搜索（FTS）包括定义全文索引的能力，FTS可以使用。 Dunno为什么你不想定义一个FTS索引如果你打算使用功能...

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在左侧用通配符（IE： LIKE'％Search'）不能使用索引（假设该列存在），保证表扫描。我没有测试&比较，但是正则表达式有同样的陷阱。要澄清 LIKE'％Search'和 LIKE'％Search％'可以不使用索引; LIKE'Search％'可以使用索引。

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.

这篇关于性能像“％Query％”对全文搜索CONTAINS查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！