Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

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)