About Full-Text Search

Full-Text Search in SQL database lets users and applications run full-text queries against character-based data in SQL tables. The LIKE operator lets users search for a specified pattern in a column and is used along with the WHERE clause.


SELECT column1, column2, ...
FROM table_name
WHERE MATCH(column)
AGAINST('text' IN BOOLEAN MODE);

Basic Syntax for Full-Text

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

Basic Syntax for LIKE

The LIKE operator is used and known by everyone who has worked with MySQL, irrespective of their level of skills and knowledge. In comparison, most people do not know what a full-text search is, let alone know that there is an alternative to the LIKE operator.

You may wonder why I need to know about the full-text search when LIKE works perfectly for me. A simple answer to your question would be speed. Normally, the LIKE operator works fine and is up to par with your expectations, but once we include a huge database, especially unstructured text data, full-text search is much faster than the LIKE operator.

Related article: Stored Procedures in MySQL

Use Conditions of Full-Text Search

LIKE operators work best for small datasets with indexed tables. They are used mostly in coding because they are easily manipulated and changeable. They can be used with or without an indexed column.

Full-text search, on the other hand, is for larger datasets. They are used mostly when we need to search for VARCHAR, CHAR or TEXT that are in larger quantities. Unlike the LIKE operator, full-text search requires indexing. The full-text search must be researched and reviewed before applying it to actual use as they require indexed tables.

Benefits of Full-Text Search Over LIKE Operator

A full-text search facility allows you to tune the precision for better recall. If relevancy is an issue, it is better to utilize the full-text search rather than the LIKE operator.

The SQL LIKE operator can be extremely inefficient in certain scenarios. When used in an unindexed column, it will scan the whole table to find relevant matches. Matching can be performed against index keys in indexed columns but still is far less efficient than most index lookups.

Worst-case scenario, the LIKE pattern will have leading wildcards that require every index key to be examined. Meanwhile, the full-text search is optimized to handle them and often provides a ranking algorithm to check the search results for the searched keywords.

✍️
There are three modifiers to use with the full-text search: a. IN NATURAL LANGUAGE MODE b. IN BOOLEAN MODE c. WITH QUERY EXPANSIONThese can be changed and adjusted according to the user's needs, such as "in Natural Language mode with Query expansion", "in Boolean mode with Query expansion", ...

Features of Full-Text Search

Search time

Adding a full-text search allows a greater reduction of search time. A large data set search using the LIKE operator with indexing may require some minutes, but with the same conditions, the full-text search would require only a few seconds.

Ngram full-text parser

A major addition to search with full-text search is the addition of morphology. MySQL provides a built-in, full-text ngram parser that supports Chinese, Japanese, and Korean (CJK) languages and an installable MeCab full-text parser plugin for Japanese. Full-text search utilizes this to search within diverse morphology.


Also read: Building and Publishing an NX NextJS Library

Precision and Recall

Precision is the fraction of relevant instances among the retrieved instances, whereas recall is the fraction of retrieved relevant instances. Precision is seen as a quality measure, and recall is seen as a quantity measure. In other words, high precision means that more relevant results and fewer unrelated results are shown to the searcher (no false positives). On the other hand, high recall implies that few relevant results are missing from the search list (no false negatives).

You probably do hundreds of searches daily, like searching for songs on YouTube, shopping on e-commerce sites, googling cute cat videos or looking for alternatives to MySQL "LIKE".

For instance, you are considering buying a pair of black boots and searching for them on an e-commerce site. Suppose it gave you ten different search results: six were black boots, two were brown boots, one was black shoes, and the other was black socks. The precision for it would be 6 out of 10, i.e. 60%.

If the site had ten products relevant to black boots, then its recall would be 60% as it had four more relevant products that weren't shown to the user. This may seem like an ignorable aspect on a small scale. However, on a large scale, it would infer that the user either had to dig deep into your site to find what they were looking for or you lost a potential sale.

There will always be a trade-off between 'precision' and 'recall'. High precision means that fewer irrelevant results are presented, while high recall means fewer relevant results are missing. Using the LIKE operator gives you 100% precision with no concessions for recall.

Conclusion

If you ever encounter a situation where the LIKE operator seems insufficient for your current needs, there are alternatives to it. You can use the full-text search feature of MySQL database, keeping in mind that it is a MySQL-specific feature.


For simple searches, implementing the LIKE operator will be enough to handle your required functionality, but for something complex like google searches, Full-text search is the way to go.



Thank you for reading. Hope you found this article helpful. Subscribe for more!