Как работают FULLTEXT INDEXES на нескольких столбцах?

При добавлении FULLTEXT INDEX в 3 столбца добавляет ли один индекс по 3 столбцам или добавляет 3 отдельных индекса?

Я спрашиваю об этом, потому что на данный момент я использую FULLTEXT следующим образом:

ALTER TABLE myTable ADD FULLTEXT all3colsIndex (col1,col2,col3);
SELECT * FROM myTable WHERE MATCH (col1, col2, col3) AGAINST ('word');

Я только что добавил пользовательский параметр в свой интерфейс поиска, где пользователь может удалить один из столбцов из поиска. Так что я могу сделать это, не теряя индекс, где я использую только 2 из 3 столбцов:

ALTER TABLE myTable ADD FULLTEXT all3colsIndex (col1,col2,col3);
If (UserOptionRemoveCol == "selected") {
    SELECT * FROM myTable WHERE MATCH (col1, col2) AGAINST ('word');
} else {
    SELECT * FROM myTable WHERE MATCH (col1, col2, col3) AGAINST ('word');
}

Или мне нужно создать новый индекс FULLTEXT для двух столбцов, а также три?

ALTER TABLE myTable ADD FULLTEXT all3colsIndex (col1,col2,col3);
ALTER TABLE myTable ADD FULLTEXT 2colsIndex (col1,col2);

Ответ 1

Взглянув на руководство для CREATE FULLTEXT INDEX, он указывает, что вы можете указать несколько столбцов, повторяя column_name как таковой:

CREATE FULLTEXT INDEX ON table_name (column_name1 [...], column_name2 [...]) ...

Учитывая эту информацию, я бы предположил, что она создает один индекс по 3 столбцам. Кроме того, я предполагаю, что он работает в соответствии с правилом "слева направо" в отношении составных индексов (я бы это подтвердил, проверив план выполнения для следующих операторов). Поэтому в этом порядке для этого должен быть выбран составной индекс (col1, col2, col3) (SELECT col1, col2 ...). Если вы должны были вызвать col2, он не будет использовать индекс.

Ответ 2

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

Из руководства :

Для полнотекстового поиска на естественном языке необходимо, чтобы столбцы, названные в функции MATCH(), будут теми же столбцами, включенными в некоторый индекс FULLTEXT в вашей таблице. [...] Если вы хотите найти название или тело отдельно, вам нужно будет создать отдельный FULLTEXT индексы для каждого столбца.

Ответ 3

Будет создан только один индекс типа FULLTEXT. Этот индекс будет "охватывать" вокруг нескольких столбцов, если это необходимо. Однако сами индексы не индексируют столбцы столько, сколько их слова. Из документация:

Индексы InnoDB FULLTEXT имеют инвертированный индексный дизайн. Инвертированные индексы сохраняют список слов, а для каждого слова - список документов, в котором появляется слово. [...]

Если вы создаете одну FULLTEXT, группирующую три столбца или три FULLTEXT, группируя только одну, эти столбцы будут фактически состоять из записей в таблицах метаданных FULLTEXT.

Когда входящие документы маркируются, отдельные слова (также называемые "токенами" ) вставляются в индексные таблицы вместе с информацией о местоположении и соответствующим идентификатором документа (DOC_ID).

В принципе, кажется, многоколоночные FULLTEXT против нескольких одноколоночных индексов FULLTEXT работают одинаково, так как хранение слов абстрагируется от первоначального разделения столбцов.