Full text search vs LIKE

问题:

My question is about using fulltext.As I know like queries which begin with % never use index :

SELECT * from customer where name like %username%

If I use fulltext for this query can ı take better performance? Can SQL Server use fulltext index advantages for queries like %username%?

回答1:

Short answer

There is no efficient way to perform infix searches in SQL Server, neither using LIKE on an indexed column, or with a fulltext index.

Long answer

In the general case, there is no fulltext equivalent to the LIKE operator. While LIKE works on a string of characters and can perform arbitrary wildcard matches against anything inside the target, by design fulltext operates upon whole words/terms only. (This is a slight simplification but it will do for the purpose of this answer.)

SQL Server fulltext does support a subset of LIKE with the prefix term operator. From the docs (http://msdn.microsoft.com/en-us/library/ms187787.aspx):

SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');

would return products named chainsaw, chainmail, etc. Functionally, this doesn't gain you anything over the standard LIKE operator (LIKE 'Chain%'), and as long as the column is indexed, using LIKE for a prefixed search should give acceptable performance.

The LIKE operator allows you to put the wildcard anywhere, for instance LIKE '%chain', and as you mentioned this prevents an index from being used. But with fulltext, the asterisk can only appear at the end of a query term, so this is of no help to you.

Using LIKE, it is possible to perform efficient postfix searches by creating a new column, setting its value to the reverse your target column, and indexing it. You can then query as follows:

SELECT Name
FROM Production.Product
WHERE Name_Reversed LIKE 'niahc%'; /* "chain" backwards */

which returns products with their names ending with "chain".

I suppose you could then combine the prefix and reversed postfix hack:

SELECT Name
FROM Production.Product
WHERE Name LIKE 'chain%'
AND Name_Reversed LIKE 'niahc%';

which implements a (potentially) indexed infix search, but it's not particularly pretty (and I've never tested this to see if the query optimizer would even use both indexes in its plan).

回答2:

You have to understand how index is working. Index is the very same like the dead-wood edition of encyclopedia.

If you use:

SELECT * from customer where name like username%

The index, in fulltext or no fulltext should work. but

SELECT * from customer where name like %username%

will never work with index. and it will be time-consuming query.

回答3:

Of what I know about fulltext indexes, i'll make the following extrapolations:

1. Upon indexing, it parses the text, searching for words (some RDBMS, like MySQL, only consider words longer than 3 chars), and placing the words in the index.
2. When you search in the fulltext index, you search for words, which then link to the row.
3. If I'm right about the first two (for MSSQL), then it will only work if you search for WORDS, with lengths of 4 or more characters. It won't find 'armchair' if you look for 'chair'.

Assuming all that is correct, I'll go ahead and make the following statement: The fulltext index is in fact an index, which makes search faster. It is large, and has fewer search posibilities than LIKE would have, but it's way faster.

More info:
http://www.developer.com/db/article.php/3446891
http://en.wikipedia.org/wiki/Full_text_search

回答4:

Like and contains are very different -

Take the following data values

'john smith' 'sam smith' 'john fuller'

like 's%' 'sam smith'

like '%s%' 'john smith' 'sam smith'

contains 's'

contains 'john' 'john smith' 'john fuller'

contains 's*' 'john smith' 'sam smith'

contains s returns the same as contains s* - the initial asterisk is ignored, which is a bit of a pain but then the index is of words - not characters

回答5:

https://stackoverflow.com/users/289319/mike-chamberlain, you are quite right as you suggest it's not enough to search something 'chain' WHERE Name LIKE 'chain%' AND Name_Reversed LIKE 'niahc%' is not equivalent to like'%chain%'****