What is the difference between Full Text Search and LIKE?

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

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

FTS is often faster and more accurate in most cases, but it is more powerful but has a space consuming to maintain index structures. LIKE can be as fast or faster than FTS in some cases. There are cases where LIKE may not be as fast, but it is fast enough.

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

LIKE in 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 in 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"

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

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

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

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

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

So deciding which one to use depends on the purpose of your system and your 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, FTS is more recommended. If an exact word search is enough for you, LIKE can do the job.