Оптимизация MySQL LIKE '% string%' запросов в innoDB

Наличие этой таблицы:

CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Мы хотели бы оптимизировать следующий запрос:

SELECT id FROM example WHERE keywords LIKE '%whatever%'

В таблице есть InnoDB (так что теперь нет FULLTEXT), который был бы лучшим индексом для оптимизации такого запроса?

Мы пробовали простую:

ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);

Но запрос на объяснение показывает, что нужно сканировать таблицу целиком если наши запросы, где LIKE "независимо от%", этот индекс работает хорошо, но в противном случае не имеет значения.

Есть ли способ оптимизировать это для innoDB?

Спасибо!

Ответ 1

Индексы строятся от начала строки до конца. Когда вы используете предложение типа LIKE 'whatever%', MySQL может использовать эти начальные индексы для поиска whatever очень быстро.

Но переход на LIKE '%whatever%' удаляет этот якорь в начале строки. Теперь начальные индексы не могут использоваться, потому что ваш поисковый запрос больше не привязан к началу строки - он "плавает" где-то посередине, и все поле должно быть поиском. Любой запрос LIKE '%... никогда не может использовать индексы.

Вот почему вы используете полнотекстовые индексы, если все, что вы делаете, - это "плавающие" поиски, потому что они предназначены для такого типа использования.

Основное примечание: теперь InnoDB поддерживает полнотекстовые индексы начиная с версии 5.6.4. Поэтому, если вы не можете обновить, по крайней мере, до 5.6.4, вам не удастся воспользоваться функцией InnoDB * и поиска в полнотекстовом формате.

Ответ 2

Я хотел бы отметить, что неожиданно создание индекса также помогло ускорить запросы для like '%abc%' запросов в моем случае.

Запуск MySQL 5.5.50 на Ubuntu (оставив все по умолчанию), я создал таблицу с большим количеством столбцов и вставил 100,000 фиктивные записи. В одном столбце я вставил полностью случайные строки с 32 символами (т.е. Все они уникальны).

Я выполнил несколько запросов, а затем добавил индекс в этот столбец. Простой

select id, searchcolumn from table_x where searchcolumn like '%ABC%'

возвращает результат в ~2 seconds без индекса и в 0.05 seconds с индексом.

Это не соответствует приведенным выше объяснениям (и во многих других сообщениях). Что может быть причиной этого?

ИЗМЕНИТЬ Я проверил вывод EXPLAIN. На выходе указано, что строки 100,000, но дополнительная информация - "Using where; Using index". Итак, каким-то образом СУБД должна искать все строки, но все же может использовать индекс?