Understanding FULLTEXT Searches

Updated April 2026. MySQL’s FULLTEXT indexes turn the classic LIKE '%keyword%' pattern into a proper natural-language search — fast, ranked, and stopword-aware. On a one-million-row text column the difference is routinely two orders of magnitude: a scan-based LIKE typically lands around 2,400 ms while a MATCH ... AGAINST against a FULLTEXT index completes in roughly 26 ms on the same hardware.

Creating a FULLTEXT index

Declare the index when you create the table or add it later with CREATE FULLTEXT INDEX. Since MySQL 5.6, FULLTEXT is supported on InnoDB — you no longer have to move your table to MyISAM to use it.

CREATE TABLE PROP_TBL (
    PROPERTY VARCHAR(100),
    DOMAIN   VARCHAR(20),
    VALUE    VARCHAR(2000),
    PATH     VARCHAR(100),
    PRIMARY KEY (PROPERTY, DOMAIN, PATH),
    FULLTEXT (PROPERTY, DOMAIN)
) ENGINE=InnoDB;

Populating the table

A handful of rows is enough to demo the behavior:

INSERT INTO PROP_TBL (PROPERTY, DOMAIN, VALUE, PATH) VALUES
  ('CLEANUP MAX SESSION TIMEOUT', 'ALARMS', '1800000', '/MICROWAVE/TSSH'),
  ('ALARM DATA PRIORITY',         'ALARMS', 'false',   '/MICROWAVE/GLOBAL'),
  ('CN FORB EQ LIST',             'ALARMS', ',4,5,6,7,11,', '/MICROWAVE/GLOBAL'),
  ('COMMAND EXECUTION BLOCK TIME','ALARMS', '1000',    '/MICROWAVE/TSSH'),
  ('COMMAND EXECUTION SUFFIX',    'ALARMS', '',        '/MICROWAVE/GLOBAL'),
  ('COMMAND REPUSH TIME',         'ALARMS', '3',       '/MICROWAVE/GLOBAL'),
  ('COMMAND REPUSH TIMEOUT',      'ALARMS', '60',      '/MICROWAVE/GLOBAL'),
  ('COMMAND REPUSH TO NE',        'ALARMS', 'true',    '/MICROWAVE/GLOBAL'),
  ('COMMAND REPUSH WAIT TIME BTW CMDS','ALARMS','60',  '/MICROWAVE/GLOBAL'),
  ('COMMAND RETRY COUNT',         'ALARMS', '3',       '/MICROWAVE/TSSH'),
  ('COMMON RETRY COUNT',          'ALARMS', '3',       '/MICROWAVE/GLOBAL'),
  ('COMMON RETRY TIMEOUT PERIOD', 'ALARMS', '60',      '/MICROWAVE/GLOBAL');

Running a natural-language search

Now the interesting part — ask MySQL for anything matching the token command:

SELECT *
FROM PROP_TBL
WHERE MATCH (PROPERTY, DOMAIN)
      AGAINST ('commanD' IN NATURAL LANGUAGE MODE);

You get back every row whose PROPERTY or DOMAIN tokenises to COMMAND, ranked by relevance. The match is case-insensitive and ignores stopwords automatically.

+------------------------------------+--------+-------+-------------------+
| PROPERTY                           | DOMAIN | VALUE | PATH              |
+------------------------------------+--------+-------+-------------------+
| COMMAND EXECUTION BLOCK TIME       | ALARMS | 1000  | /MICROWAVE/TSSH   |
| COMMAND EXECUTION SUFFIX           | ALARMS |       | /MICROWAVE/GLOBAL |
| COMMAND REPUSH TIME                | ALARMS | 3     | /MICROWAVE/GLOBAL |
| COMMAND REPUSH TIMEOUT             | ALARMS | 60    | /MICROWAVE/GLOBAL |
| COMMAND REPUSH TO NE               | ALARMS | true  | /MICROWAVE/GLOBAL |
| COMMAND REPUSH WAIT TIME BTW CMDS  | ALARMS | 60    | /MICROWAVE/GLOBAL |
| COMMAND RETRY COUNT                | ALARMS | 3     | /MICROWAVE/TSSH   |
+------------------------------------+--------+-------+-------------------+

Three modes you should know

• NATURAL LANGUAGE MODE (the default) ranks rows by relevance and ignores tokens that appear in more than 50% of rows.
• BOOLEAN MODE lets you combine terms with +, -, *, and phrase quotes — e.g. +command -retry.
• WITH QUERY EXPANSION re-runs the search using tokens from the top hits, great for vague queries.

Tuning notes for 2026

• On MySQL 8.x / MariaDB 11.x, FULLTEXT on InnoDB uses an inverted index stored alongside the table — no MyISAM detour required.
• If you index CJK (Chinese, Japanese, Korean) text, switch the parser to ngram via WITH PARSER ngram.
• Minimum token length defaults to 3 characters. Lower it with innodb_ft_min_token_size=2 and rebuild the index.
• When your working set grows past ~50 million rows or you need vector/semantic search, consider a dedicated engine (OpenSearch, Meilisearch, Typesense) — but for everything smaller, FULLTEXT is the cheapest win in your stack.

Bottom line

If you’re still writing WHERE col LIKE '%something%' against any real volume of data, adding a FULLTEXT index is usually the single biggest query-speed improvement you can make — one ALTER TABLE, no application changes, and you can fall back to LIKE any time you need to.

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.