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.

MySQL 253x300 How to use MySQL Full Text Search

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)

By |December 5th, 2014|Technology|0 Comments

About the Author:

 How to use MySQL Full Text Search
Akarsh is a BI developer at e-Zest with four years of solid experience in the relevant field. He works in technologies such as ETL Pentaho, Kettle, ODI, Oracle, SAP Hana SQL Server and MySql. Akarsh is competent in BI and ETL Databases. His hobbies include reading, writing, watching movies and listening to music.

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

e-Zest | India | USA | UK | Germany | Europe