Full-Text Search with MySQL

#mysql

This article was originally published on bmf-tech.com.

Overview

MySQL has supported full-text search for quite some time, but I hadn't explored it until recently, so I decided to give it a try.

Getting Started with Full-Text Search in MySQL

Using full-text search in MySQL is significantly less cumbersome than ElasticSearch.

You can easily perform full-text searches by adding a FULLTEXT INDEX to the columns you want to search and executing a query with MATCH (col1,col2,...) AGAINST (expr [search_modifier]).

ex.

// Table with columns for FULLTEXT INDEX
CREATE TABLE `posts` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` varchar(255) DEFAULT NULL,
  `body` longtext DEFAULT NULL,
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

// Adding FULLTEXT INDEX
ALTER TABLE posts ADD FULLTEXT INDEX index_title_md_body (title, md_body) WITH PARSER ngram;

// Search query with MATCH ... AGAINST
SELECT
  *
FROM
  posts
WHERE MATCH (title, body)
AGAINST ("MySQLで全文検索" IN BOOLEAN MODE)

FULLTEXT INDEX can also be added using CREATE TABLE or CREATE INDEX.

Full-Text Parsers

MySQL's full-text search supports ngram and MeCab parsers.

By default, ngram is set.

If you want to use MeCab, you need to install the plugin.

Full-Text Search Modes

There are three modes available, and you can specify which one to use.

The search results vary depending on the mode, so you can choose based on the search experience you want to provide.

NATURAL LANGUAGE MODE
- A mode that searches using natural language processing
- cf. Natural Language Full-Text Searches
BOOLEAN MODE
- A mode that searches using conditions like AND, OR, NOT
- cf. Boolean Full-Text Searches
QUERY EXPANSION
- A mode that adds synonyms or related words to the search terms
- cf. Full-Text Searches with Query Expansion

Adjusting Search Precision

Apart from parsers and full-text search modes, there are other approaches to adjust the nature of the search:

Changing parser settings
- ex. Changing ngram token size
Specifying character set
- ex. utf8_general_ci, utf8_unicode_ci etc...
Adjusting full-text stopwords
- cf. Full-Text Stopwords

Refer to Fine-Tuning MySQL Full-Text Search as well.

Impressions

I incorporated MySQL's full-text search feature into this blog.

Search Article List

It seems to perform better than LIKE searches, but actual performance maintenance may vary by environment. However, if requirements are met, it is a sufficiently usable feature.

DEV Community