Full text search vs LIKE

Question

My question is about using full text search. 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 I take better performance? Can SQL Server use fulltext index advantages for queries like %username%?

Dervish

Answer 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).

Milestone

Very creative idea on combining the prefix and reversed postfix hack! Would have never thought of that! – Adjudge

Is this still the case with SQL Server 2012 and 2014? =( – Marigraph

So you are saying in a case where you had something like: Select * From People where FullName like '%john%' It would be more efficient to do something more like this: Select * From People where FirstName like 'john%' or LastName like 'john%' Does that make sense? – Unfasten

Potentially yes, if both first and last names are indexed. The queries are not exactly equivalent, however, as the second query would not find someone with the surname Littlejohn. – Milestone

"But with fulltext, the asterisk can only appear at the end of a query term, so this is of no help to you." +1 – Savage

Note: the reversed trick WHERE Name_Reversed LIKE 'niahc%' is useful to search for %chain, nice indeed! BUT your last code block is wrong: you do name LIKE 'chain%' AND Name_reversed LIKE 'niahc%' => this means it must begin by "chain" AND finish by "chain", so the only candidate is "chain"... So your AND is incorrect. OR would be slightly better, but, still, not equivalent to %chain%. – Savage

Answer 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.

Rhizomorphous

Ok Ran thank you very much for your comment I am agree with you about working mechanishm for index.(Like encyclopedia).But fulltext much faster(1/20) than like query for our situation( SELECT * from customer where name like %username% ).Actually I wonder How it achive this? – Dervish

developer.com/db/article.php/3446891 LIKE is totally different than fulltext searches. Only a simple index on a char/varchar attribute would work in your first query. No fulltext index is used in this case. – Antarctica

Answer 3

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

Quadroon

Answer 4

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

Antarctica

Answer 5

You can use:

SELECT * from customer where CONTAINS(name, 'username')

OR

SELECT * from customer where FREETEXT(name, 'username')

Unbar

Answer 6

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%'****

Ways