What is the difference between Full Text Search and LIKE?

Question

I’ve heard a lot about the term Full Text Search (FTS) lately, and I’ve been told that I should use this instead of drafting the queries with LIKE. But how to use the Full Text Search? What are the advantages and disadvantages? And when is it really best to use FTS instead of the traditional and simple LIKE?

Answer 1

LIKE is usually a simple mechanism to be used in expressions comparing to various texts in the database, so it reads the required data in the expression and applies the LIKE which is a simplified form of regular expression.

The FTS is a inverted indexing of database data. Technically the entire database can be the target of this indexing. The keys in this case become words and they point to all places (which were considered in the index) where these words appear. In the more sophisticated can be control of relevance, proximity, partial word, context, etc.

The FTS is usually faster and more accurate in most cases, as well as being more powerful, but has a space consumption to maintain index structures. The LIKE may be as or faster than FTS in some cases. There are cases where the LIKEmay not be that fast, but it’s fast enough.

Each database usually has a very different FTS than the other while the LIKE is more or less standard.

LIKE in the Sqlite:

SELECT FROM tabela WHERE coluna LIKE 'teste%' //costuma ser eficiente com índice apropriado
SELECT FROM tabela WHERE coluna LIKE '%teste%' //qualquer coisa que tenha 'teste' no meio
SELECT FROM tabela WHERE coluna LIKE '%teste' //termina com teste
SELECT FROM tabela WHERE coluna LIKE 'teste_' //termina com um e apenas um caractere qq
SELECT FROM tabela WHERE coluna LIKE '_teste_' //teste no meio de 1 caractere na ponta

FTS on the Sqlite:

CREATE VIRTUAL TABLE tabela USING fts3 (col1, col2, text );
INSERT INTO tabela VALUES ('3', 'testo', 'Este é um exemplo');
INSERT INTO tabela VALUES ('24', 'exemplo', 'Ok, está bom assim');
INSERT INTO tabela VALUES ('13', 'outro', 'Finalizando');
SELECT * FROM tabela WHERE tabela MATCH "exemplo"

I put in the Github for future reference.

Answer 2

LIKE is used for string searching in any string fields. This field can be char, varchar, text, etc. Whenever we search the database using the operator LIKE, it loads each row of the database for comparison. Then discard lines that do not match the given criteria. This uses a lot of memory and time to execute the query. As operation is only preferred for small database with few lines.

In the case of text search FULL TEXT SEARCH uses INDEXES for research. In this case, the search occurs in the index table. Uses fewer resources for query execution but uses more space to create INDEXES.

Another difference is that the LIKE is not malleable and returns only the items that correspond 100% while the FULL TEXT SEARCH offers better control and can even bring the records close to those of your filter.

Answer 3

Full Text Search is a technique that uses word indexing within a text field, thus allowing you to quickly search through many records. Already the LIKE searches through the string of characters within the field, that is, comparing the text letter by letter.

FTS is more complex, and requires a database manager preparation before it can be used. Already the LIKE is part of the SQL itself, and only needs to be part of the query.

Therefore, deciding which of the two uses depends on the purpose of your system and its database structure. If it is interesting for your system that the user can search faster and get results close to what he is looking for, the FTS is most recommended. If an exact word search is sufficient for you, the LIKE can do the job.