How to implement full-text search in MySQL

Introduction

MySQL supports full-text search through the FULLTEXT index type, available in InnoDB since MySQL 5.6. It is more powerful than LIKE '%term%' because it supports relevance ranking, boolean mode, and natural language processing.

Creating a Full-Text Index

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  body TEXT,
  FULLTEXT idx (title, body)
) ENGINE=InnoDB;

Querying

Use MATCH ... AGAINST to search:

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

Boolean mode allows explicit inclusion and exclusion:

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

For very large datasets, you may eventually move to dedicated search engines, but MySQL full-text is an excellent starting point. Proper indexing strategy is covered in what are database indexes and how do they work. If you need backup procedures before adding indexes, see how to back up and restore a MySQL database.