MySQL полнотекстовый запрос по нескольким столбцам

Question

У меня проблема, с которой я сталкиваюсь при использовании полнотекстового поиска в MySQL. Я использую MySQL 5.1.67. Вот моя упрощенная таблица:

CREATE TABLE `contact_info` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) DEFAULT NULL,
`last_name` varchar(35) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_ft_first_name_last_name` (`first_name`,`last_name`)
) ENGINE=MyISAM CHARSET=latin1;

Я хочу запросить имя и фамилию в таблице с около 5 миллионов строк. Имя и фамилия в отдельных столбцах, как показано в таблице выше. Я хочу получить все строки, где имя и фамилия "Джон Смит". Я не хочу, чтобы все люди назвали Джона по имени или Смит по фамилии. Я знаю, что могу сделать что-то вроде следующего, но поскольку пользователи иногда предпочитают сохранять полное имя в столбце first_name или в столбце last_name, я пытаюсь настроить полнотекстовый поиск.

SELECT ci.first_name,ci.last_name FROM contact_info ci
WHERE  ci.first_name LIKE 'John%' AND ci.last_name LIKE 'Smith%' 

Когда я запускаю запрос, заставляющий его использовать индекс первичного ключа, он возвращает несколько сотен записей для Джона Смита, это то, что я хочу, но это медленно и занимает около 5 секунд. Вот запрос:

SELECT ci.first_name,ci.last_name FROM contact_info ci USE INDEX(PRIMARY)
WHERE MATCH(ci.first_name,ci.last_name) AGAINST ('"John Smith"' IN BOOLEAN MODE);

Когда я запускаю запрос с индексом, который предпочитает оптимизатор, он ничего не возвращает.

SELECT ci.first_name,ci.last_name FROM contact_info ci USE INDEX(idx_ft_first_name_last_name)
WHERE MATCH(ci.first_name,ci.last_name) AGAINST ('"John Smith"' IN BOOLEAN MODE);

Почему он ничего не возвращает? Разве нельзя запросить "Джона Смита" по двум столбцам с таким полнотекстовым индексом, как этот?

Answer 1

Документация для поиска FULLTEXT гласит:

Фраза, заключенная в символы двойной кавычки ("" "), соответствует только строкам, которые содержат фразу буквально, как она была набрана. Полнотекстовая машина разбивает фразу на слова и выполняет поиск по индексу FULLTEXT для слов. Несловарные символы не обязательно должны совпадать: для поиска по фразе требуется, чтобы совпадения содержали точно такие же слова, что и фраза, и в том же порядке, например, "тестовая фраза" соответствует "тест, фраза".

Вы поставили фразу "John Smith" в двойные кавычки. Но у вас нет ни одного столбца, содержащего эту фразу: он разбит по столбцам.

Попробуйте отбросить двойные кавычки.

Попробуйте ("Джон и Смит" в булевом режиме) и посмотрите, что вы получите. Это может сработать. Кроме того, FULLTEXT должен ранжировать записи, где оба слова совпадают, выше, чем те, где только одно совпадение.