Полнотекстовый поиск Innodb не работает, MyIsam возвращает результаты

Есть несколько различий между FULLTEXT MyISAM и InnoDB. Я думаю, вы были пойманы на обработке «коротких» слов и/или стоп-слов. MyISAM покажет строки, а InnoDB — нет.

Что я сделал при использовании FT (и после перехода на InnoDB), так это отфильтровал ввод пользователя, чтобы избежать коротких слов. Это требует дополнительных усилий, но дает мне желаемые строки. Мой случай немного отличается, так как результирующий запрос выглядит примерно так. Обратите внимание, что я добавил + для обязательных слов, но не для слов короче 3 (мой ft_min_token_size равен 3). Эти поиски были для build a table и build the table:

WHERE match(description) AGAINST('+build* a +table*' IN BOOLEAN MODE)
WHERE match(description) AGAINST('+build* +the* +table*' IN BOOLEAN MODE)

(Конечный * может быть излишним; я не исследовал это.)

Другой подход

Поскольку FT очень эффективен для некоротких, непрерывных слов, выполняйте поиск в два этапа, каждый из которых является необязательным: Чтобы найти «длинное слово», выполните

WHERE MATCH(d) AGAINST ('+long +word' IN BOOLEAN MODE)
  AND d REGEXP '[[:<:]]a[[:>:]]'

Первая часть быстро сокращает возможные строки, ища «длинное» и «слово» (как слова). Вторая часть также проверяет, есть ли в строке словоa. REGEXP стоит дорого, но будет применяться только к тем строкам, которые проходят первый тест.

Для поиска только что по "длинному слову":

WHERE MATCH(d) AGAINST ('+long +word' IN BOOLEAN MODE)

Для поиска только что по слову "a":

WHERE d REGEXP '[[:<:]]a[[:>:]]'

Предостережение: этот случай будет медленным.

Примечание. В моих примерах слова могут быть в любом порядке и в любом месте строки. То есть эта строка будет совпадать во всех моих примерах: «Она ждала от него слова».

Вот пошаговая процедура, которая должна была воспроизвести вашу проблему. (На самом деле именно так вы должны были написать свой вопрос.) Среда представляет собой недавно установленную виртуальную машину с Дебиан 9.8 и Сервер Percona версии 5.6.43-84.3.

1. Создайте таблицу ИнноБД с полнотекстовый индекс и некоторыми фиктивными данными:

   create table test.ft_innodb (
       txt text,
       fulltext index (txt)
   ) engine=innodb charset=utf8 collate=utf8_unicode_ci;
   
   insert into test.ft_innodb (txt) values
       ('Some dummy text'),
       ('Text with a long and short stop words in it ex');
   

2. Выполните тестовый запрос, чтобы убедиться, что он еще не работает так, как нам нужно:

   select txt
       , match(t.txt) against ('+some' in boolean mode) as score0
       , match(t.txt) against ('+with' in boolean mode) as score1
       , match(t.txt) against ('+in'   in boolean mode) as score2
       , match(t.txt) against ('+ex'   in boolean mode) as score3
   from test.ft_innodb t;
   

   Результат (округлено):

   txt                                            | score0 | score1 | score2 | score3
   -----------------------------------------------|--------|--------|--------|-------
   Some dummy text                                | 0.0906 | 0      | 0      | 0
   Text with a long and short stop words in it ex | 0      | 0      | 0      | 0
   

   Как видите, он не работает со стоп-словами ("+with") или с короткими словами ("+ex").

3. Создайте пустую таблицу ИнноБД для пользовательских стоп-слов:

   create table test.my_stopwords (value varchar(30)) engine=innodb;
   

4. Отредактируйте /etc/mysql/my.cnf и добавьте следующие две строки в блок [mysqld]:

   [mysqld]
   # other settings
   innodb_ft_server_stopword_table = "test/my_stopwords"
   innodb_ft_min_token_size = 1
   

5. Перезапустите MySQL с помощью service mysql restart

6. Запустите запрос из (2.) еще раз (результат должен быть таким же)

7. Восстановите полнотекстовый индекс с помощью

   optimize table test.ft_innodb;
   

   Это фактически перестроит всю вкладку, включая все индексы.

8. Выполните тестовый запрос из (2.) еще раз. Теперь результат:

   txt                                            | score1 | score1 | score2 | score3
   -----------------------------------------------|--------|--------|--------|-------
   Some dummy text                                | 0.0906 | 0      | 0      | 0
   Text with a long and short stop words in it ex | 0      | 0.0906 | 0.0906 | 0.0906
   

Вы видите, что это работает просто отлично для меня. И это довольно просто воспроизвести. (Опять же - именно так вы должны были написать свой вопрос.)

Поскольку ваша процедура скорее хаотична, чем детализирована, трудно сказать, что может пойти не так. Например:

CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;

Это не содержит информации о том, в какой базе данных вы определили эту таблицу. Обратите внимание, что я поставил перед всеми своими таблицами префикс соответствующей базы данных. Теперь рассмотрим следующее: я меняю my.cnf и устанавливаю innodb_ft_server_stopword_table = "db/my_stopwords". Примечание. На моем сервере такой таблицы нет (даже схемы db не существует). Перезапустите сервер MySQL. И проверьте новые настройки с помощью

show variables like 'innodb_ft_server_stopword_table';

Это возвращает:

    Variable_name                   | Value
    --------------------------------|----------------
    innodb_ft_server_stopword_table | db/my_stopwords

И после optimize table test.ft_innodb; тестовый запрос возвращает это:

    txt                                            | score0 | score1 | score2 | score3
    -----------------------------------------------|--------|--------|--------|-------
    Some dummy text                                | 0.0906 | 0      | 0      | 0
    Text with a long and short stop words in it ex | 0      | 0      | 0      | 0.0906

Понимаете? Это больше не работает со стоп-словами. Но он работает с короткими непрерывными словами, такими как «+ex». Поэтому убедитесь, что таблица, которую вы определили в innodb_ft_server_stopword_table, действительно существует.

Распространенным методом поиска является создание дополнительного столбца с «очищенной» строкой для поиска. Затем добавьте к этому столбцу индекс FULLTEXT вместо исходного столбца.

В вашем случае удаление стоп-слов является основным отличием. Но могут быть и знаки препинания, которые можно (должны?) убрать. Иногда проблемы вызывают слова, написанные через дефис, или слова, или сокращения, или номера деталей, или номера моделей. Их можно изменить, чтобы изменить пунктуацию или интервалы, чтобы сделать их более подходящими для требований FT и / или вкуса ввода пользователя. Другое дело — добавить в столбец строки поиска слова, которые являются распространенными ошибками в написании слов, содержащихся в столбце.

Конечно, это больше работы, чем вам хотелось бы. Но я думаю, что это обеспечивает жизнеспособное решение.