JIYIK CN >

Full-text search is a database technology that retrieves records even if they do not exactly match our search criteria. This enables rich, natural language searches that feel more familiar.

Using full text search to search for "database engine" will return results containing the phrase "database", "engine", or "database engine". This resolves possible ambiguity in the search criteria, so a line like "I have a database and an engine" will still appear.

Simple searches can be done in MySQL using LIKEthe operator. This is inefficient and has limited functionality. MySQL often needs to perform a full table scan to identify records that match our query.

Full-text queries use specially created indexes to improve performance. This also enables MySQL to keep track of words in a data set, facilitating natural language searches.

Setting up full text search

We can use full-text search only on columns that have a full-text index. This is set using the -s clause CREATE TABLEin the -destination statement when seeding the database schema . We can also use it with -destination to add a search index to an existing column.FULLTEXTALTER TABLE

CREATE TABLE articles(content TEXT, FULLTEXT (content));
 
ALTER TABLE articles ADD FULLTEXT (content);

With the index in place, we can start querying the database.

Use full text search

A full text search starts with WHEREa SELECT clause. We use SELECT MATCH AGAINSTinstead of SELECT LIKE. We need to indicate the index columns to match, and the query to search for.

SELECT * FROM articles WHERE MATCH (content) AGAINST ('database engine' IN NATURAL LANGUAGE MODE);

This will perform a full text search of articles using the query database engine. By specifying IN NATURAL LANGUAGE MODE, MySQL is instructed to interpret the query literally and not process it in any way. If not specified, this is the default search mode.

Sort by results relevance

One of the advantages of full-text search is that it allows us to sort the returned records by relevance. This LIKEis not possible with a regular SELECT query. We can use MATCH ... AGAINSTthe SELECT clause as SQL SELECTpart of SELECT . The virtual column returned will contain a relevance score from 0 to 1, indicating how well the record matches the search query.

SELECT content, MATCH (content) AGAINST ('database engine') AS relevance FROM articles ORDER BY relevance DESC

This query will return the most relevant results first. This helps our application meet user expectations of how a natural language search system should perform.

MySQL calculates the search relevance score by evaluating several different factors. These include the number of records that match the query, and the number of times the query occurs in each record. Results that have multiple exact matches to the query will be ranked higher than results that contain only a partial query.

SELECTWhen using in a statement , MATCH ... AGAINSTthere is no need WHEREto repeat it in the clause. We can manually filter the results to include only records with non-zero relevance scores.

SELECT content, MATCH (content) AGAINST ('database engine') AS relevance FROM articles WHERE relevance > 0 ORDER BY relevance DESC

Query Expansion Mode

Natural language is not the only supported full-text search mode. Query expansion mode is an alternative that helps expand the scope of search results. It automatically weights search queries based on the most relevant terms.

An expanded search first finds records that contain matches to the query. These records are then examined to identify the most relevant terms. The database then runs another search, this time based on the relevant terms instead of the original query. This typically results in more records being returned while maintaining an acceptable level of relevance.

Here's how to enable query expansion mode:

SELECT * FROM articles WHERE MATCH (content) AGAINST ('database engine' WITH QUERY EXPANSION)

Boolean Mode

The last full-text search mode is the Boolean mode. This allows us to include Boolean modifiers in our queries. This mode can be used when we need advanced control over the matching logic.

We can +require a word to appear in every result by prefixing it with . Use -to exclude results containing the word. Other operators can be used to match parts of words, create sub-expressions and bring words down in relevance scores. The latter is useful when shielding "noise" terms. If we do not specify an operator, it is implied that the word will be quoted in the query using OR.

SELECT * FROM articles WHERE MATCH (content) AGAINST ('+data* engine -sqlite' IN BOOLEAN MODE)

This query will show you sqlitearticles that have at least one word that starts with a word that is not mentioned in your data. Each result may or may not contain the word engine.

Boolean mode allows us to build powerful searches using our own logic. One caveat is that it does not support search relevance scoring. This is the tradeoff for enabling users to do Boolean searches. The ranking of results may not match human expectations.

Configuring full-text search

MySQL comes with several full-text configuration options that allow us to fine-tune how searches are performed. Here are some of the most important ones.

• innodb_ft_min_token_size – Sets the minimum word length for indexed terms. Words with fewer characters than this value will not be added to the index, so we will not be able to search for them. The default value is 3, which excludes extremely common words such as a, an, and I. This setting applies to InnoDB tables; use for MyISAM ft_min_word_len.
• innodb_ft_max_token_size - Similar to innodb_ft_min_token_size, this sets the maximum length of an indexed word. Longer words will not be searchable. Use with MyISAM tables ft_max_word_len.
• innodb_ft_enable_stopword – By default, this setting allows us to control whether MySQL should filter out “stop words”. Stop words are very common words that can overly affect search results. The default stop word list contains 36 common phrases.
• innodb_ft_user_stopword_table – We can set this value to the name of the database table from which MySQL should get the stopword list. The table must have a VARCHARcolumn called value. Add our stopwords to exclude as records in this table.

These settings are usually set in our MySQL server configuration file. The location varies by platform; it can usually be found in /etc/mysql/my.cnf . You will need to restart the MySQL service after changing the settings.

Once the server is back up, rebuild the full-text indexes for the table. We must do this so that our data is re-indexed using the current configuration. Otherwise, the previously indexed data will continue to be used.

To reindex an InnoDB table, run OPTIMIZE TABLE my_table. For a MyISAM table, use REPAIR TABLE my_table QUICK. The search index will then be rebuilt so that our configuration changes take effect.

Summarize

We enable MySQL full-text search by adding a full-text index to a searchable field. We then use MATCH ... AGAINSTand one of three search modes to get results. Natural language queries return a search relevance score that we can use to rank the results.

Full-text search provides LIKEmore powerful search behavior than the SELECT statement. It is also more performant, especially on large data sets, because all the text is pre-indexed.