How to use MySQL Full-Text Search

MySQL support text search with the help of LIKE operator and Regular expressions. However, it is not a full-fledged text search as it does not provide relevant ranking of search results, results for complex queries and is not performance oriented.

To overcome these limitations, MySQL has come up with a new feature called full-text search. It is available only for InnoDb and MyISAM engines after version 5.6. Full-text search uses a sophisticated algorithm to eliminate drawbacks.

It has the following features:

• Simplicity: You use SQL queries to use the full-text search.
• Fully dynamic index: MySQL automatically indexes the text column whenever the data changes. You don’t need to run the index periodically.
• Moderate index size: It does not take much memory to store the index.
• High Performance: It is fast to search based on complex search query.

How to use it:

• To use full-text search is simple, all you have to do is to index the required column of searching by FULLTEXT index type. This is the usual Create Table or Alter Table query.

Example:

ALTER TABLE table_name ADD FULLTEXT(column_name1, column_name2,…)

• Next use MATCH() and AGAINST() keyword to query full text search.

Example

For searching ‘Classic’ keyword in productline column (this column should be FULLTEXT Indexed)

SELECT productName, productline FROM products WHERE MATCH(productline) AGAINST('Classic')

For searching ‘Classic’ AND/OR VINTAGE keyword in productline column (rows with both keywords will have a higher rank)

SELECT productName, productline FROM products WHERE MATCH(productline) AGAINST('Classic,Vintage').

It’s as simple as that! MySQL full-text search is a really cool and simple feature to use.

Reference: http://www.mysqltutorial.org/introduction-to-mysql-full-text-search.aspx

(Photo: Wikimedia)