%searchKeyword%上的SQL Server FullText [英] SQL Server FullText on %searchKeyword%
问题描述
我在想,如果SQL Server实际上在文本中间的搜索中使用了全文索引(比如LIKE%searchKeyword%)?
我忘了我在哪里读过它,但我确实在某处读到全文索引只会在以StartsWith方式(searchKeyword%)使用时才被使用,您可以在其中搜索具有固定开头和变量结尾的LIKE。如果这是真的,请让我知道吗?
( CONTAINS()和 FREETEXT())来访问索引(如果您拥有它们,它们并未默认启用),而不是 LIKE strong>运算符。$ b LIKE 运算符可以使用正常索引(例如,CREATE INDEX ix_tbl_fld ON mytable(textfield))但是当模式以%通配符开始时,这些搜索需要扫描表中的所有值。
如果您有使用SQL Server 2008下的表压缩可以提高 LIKE'%keyword%'搜索的性能,即使没有索引,我也可以重复使用很多具有相同值的行没有,所以我不能测试这个理论......)。
I was wondering if SQL Server is actually utilizing its fulltext index on searches that can be in the middle of text (such as LIKE %searchKeyword%)?
I forgot where I read it, but I did read somewhere that the fulltext index would only be utilized when it is used in a "StartsWith" manner (searchKeyword%) where you search for a LIKE with a fixed beginning and a variable ending.
Please let me know if this is true?
Full-text indexes (if you have them, they aren't enabled by default) are accessed using functions like CONTAINS() and FREETEXT(), not the LIKE operator.
Normal indexes (e.g., "CREATE INDEX ix_tbl_fld ON mytable(textfield)") can be used by the LIKE operator, but not when the pattern begins with a % wildcard... these searches require a scan of all of the values in the table.
If you have a lot of rows with the same value repeated, using table compression under SQL Server 2008 may improve performance of LIKE '%keyword%' searches, even without an index (I don't have it, so I can't test this theory...).
这篇关于%searchKeyword%上的SQL Server FullText的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!