How to do full-text search in MySQL

16/08/2014 - MYSQL

If your MySQL version is less than 5.6 then you can only use MyISAM storage engine for full-text search. MyISAM is not favoured by many, mainly because it lacks of transaction (ACID) support, doesn't provide referential integrity, isn't crash safe and has table-level locks which makes it very slow in heavy read+write processes. I suggest upgrading MySQL at least to 5.6 and using InnoDB for full-text search.


Facts



Dummy table


# Create table.
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `middlename` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `surname` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `code` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT(`name`,`middlename`,`surname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# Populate table.
INSERT INTO `person` (`name`, `middlename`, `surname`, `code`) VALUES
('John', 'Joseph', 'Travolta', 'JJT'),
('John', '', 'Lenon', 'JL'),
('John', '', 'Wayne', 'JW'),
('John', 'Paul', 'John', 'JPJ'),
('Robert', '', 'DeNiro', 'RD'),
('Elton', '', 'John', 'EJ'),
('Abi', 'John John', '', 'AJ'),
('Johny', '', '', 'J'),
('John', 'John', 'John', 'JJJ');

Example queries


These example use IN NATURAL LANGUAGE MODE search modifier where words that are present in 50% or more of the rows are considered common and do not match so for that reason I would suggest you to use IN BOOLEAN MODE search modifier instead.


# Basic select query.
SELECT *
FROM person
WHERE
MATCH(`name`, `middlename`, `surname`) AGAINST ('John' IN NATURAL LANGUAGE MODE);

+----+-------+------------+----------+------+
| id | name  | middlename | surname  | code |
+----+-------+------------+----------+------+
|  9 | John  | John       | John     | JJJ  |
|  4 | John  | Paul       | John     | JPJ  |
|  7 | Abi   | John John  |          | AJ   |
|  1 | John  | Joseph     | Travolta | JJT  |
|  2 | John  |            | Lenon    | JL   |
|  3 | John  |            | Wayne    | JW   |
|  6 | Elton |            | John     | EJ   |
+----+-------+------------+----------+------+
7 rows in set (0.00 sec)

# Calculation the search result relevance (score) helps us for ordering purposes.
SELECT *,
MATCH(`name`, `middlename`, `surname`) AGAINST ('John' IN NATURAL LANGUAGE MODE) AS score
FROM person
ORDER BY score DESC;

+----+--------+------------+----------+------+----------------------+
| id | name   | middlename | surname  | code | score                |
+----+--------+------------+----------+------+----------------------+
|  9 | John   | John       | John     | JJJ  |    0.035737544298172 |
|  4 | John   | Paul       | John     | JPJ  | 0.023825030773878098 |
|  7 | Abi    | John John  |          | AJ   | 0.023825030773878098 |
|  1 | John   | Joseph     | Travolta | JJT  | 0.011912515386939049 |
|  2 | John   |            | Lenon    | JL   | 0.011912515386939049 |
|  3 | John   |            | Wayne    | JW   | 0.011912515386939049 |
|  6 | Elton  |            | John     | EJ   | 0.011912515386939049 |
|  5 | Robert |            | DeNiro   | RD   |                    0 |
|  8 | Johny  |            |          | J    |                    0 |
+----+--------+------------+----------+------+----------------------+
9 rows in set (0.00 sec)