Practical Guide to Using MySQL Full-Text Indexes

After introducing the basic principle of full‑text indexing, this article shows how to effectively use MySQL full‑text indexes.

Ordinary SQL queries look like:

select * from tb1 where id in (1,2);
select * from tb1 where id < 10;

Full‑text search uses the syntax:

MATCH (col1,col2,...) AGAINST (expr [search_modifier])
search_modifier:
{ IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION }

A sample table fx with 100 000 rows is created as follows:

CREATE TABLE `fx` (
  `id` int NOT NULL AUTO_INCREMENT,
  `s1` varchar(200) DEFAULT NULL,
  `log_time` datetime DEFAULT NULL,
  `s2` varchar(200) DEFAULT NULL,
  `s3` text,
  PRIMARY KEY (`id`),
  KEY `idx_log_time` (`log_time`)
)

A full‑text index is added on column s1 and a simple search for the keyword “cluster” is performed:

alter table fx add fulltext ft_s1(s1);
select count(*) from fx where match(s1) against ('cluster');

The three full‑text search modes are demonstrated:

Natural Language Mode (default) – optimal query:

# SQL 1
select count(*) from fx where match(s1) against ('cluster' in natural language mode);

More explicit forms (SQL 2, SQL 3) are shown, with execution‑plan analysis indicating that SQL 1 is the most efficient, SQL 2 is second, and SQL 3 performs a full table scan.

Relevance scoring can be retrieved and sorted, e.g.:

# SQL 4
SELECT MATCH (s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) AS relevance
FROM fx
WHERE MATCH (s1) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) > 0;

Boolean mode allows logical operators (+ for AND, - for NOT, space for OR) to combine multiple keywords:

# SQL 8
select s1 from fx where match(s1) against ('+mysql +oracle' in boolean mode);
# SQL 9
SELECT s1 FROM fx WHERE MATCH (s1) AGAINST ('+mysql +oracle -postgresql -mongodb -sqlserver' IN BOOLEAN MODE);
# SQL 10
select s1 from fx where match(s1) against ('mysql oracle' in boolean mode);
# SQL 11
select s1 from fx where match(s1) against ('+mysql +oracle >postgresql' in boolean mode);

Query‑expansion mode automatically adds related keywords to the search, demonstrated on a smaller table fx_few :

select * from fx_few where match(s1) against ('sqlserver' with query expansion);

The article concludes that while Boolean mode offers flexible keyword filtering, natural language mode should be used when relevance‑ordered results are required, and promises future coverage of accuracy improvement and Chinese‑language plugins.