Search This Blog

Tuesday, November 13, 2018

Full text search in MySQL

A full-text search makes use of indexes. These indexes are setup on specific fields of a MySQL table, and optimizes the way that MySQL stores the records for that particular table.

If you have a database that contains: name, price, description and picture of some sporting goods. When users perform a search, they will most commonly enter part of the product name or description.

When you setup an full-text index on the product name and description field in your MySQL database, MySQL automatically stores the records in a indexed format, which means that MySQL can search the data in the fields very fast and efficient in terms of server resources. Compared to the traditional LIKE command in MySQL there is a lot performance to gain by switching to full-text search.

Other characteristics and benefits of full-text are:
  • Searches are not case sensitive
  • Short words are ignored, the default minimum length is 4 characters.
  • Very common words like “i”, “in”, “on”, also called stopwords are ignored. You can see a list of the standard MySQL stopwords here.
  • You can disable stopwords by setting the variable in the MySQL configuration.
  • Fulltext searches can only be made on “text” fields.
  • If a word is present in more than 50% of the rows, it will have a weight of zero. This means that there will be no search results. This is mostly a problem if you’re testing with a limited dataset
  • MySQL requires that you have at least three rows of data in your result set before it will return any results.
  • By default, your search query must be at least four characters long and may not exceed 254 characters.
To create the full-text index that enables the full-text search. 

ALTER TABLE `Table_to_be_full_searchable` ADD FULLTEXT `search_index` (
<field 1>, <field n>)
Here is the example we’ll build a index that can search in the fields “name” and “description”.

ALTER TABLE `I88CA_Items` ADD FULLTEXT `search_index` (
`name` ,
`description`)
The basic syntax for a full-SQL query is:

SELECT * from <table> WHERE MATCH (<field 1>, <field n>) AGAINST(‘<search word>’)
 Example of using this SQL:

SELECT * from I88CA_Items WHERE MATCH (name, description) AGAINST(‘i88.ca’)

we can add a ranking of the results, so the best matches will be displayed first:

SELECT *,  MATCH(name, description) AGAINST (‘i88.ca’) AS score from I88CA_Items WHERE MATCH (name, description) AGAINST(‘i88.ca’) order by score desc