Производительность REGEXP (сравните с "LIKE" и "=" )

Я использую MySQL. Я задал вопрос о том, как запросить в базе данных одно совпадение слов здесь.

Есть ответ, который предлагает мне использовать REGEXP '[[:<:]]word[[:>:]]'

Это хороший ответ, однако, я не уверен, как эта вещь REGEXP '[[:<:]]word[[:>:]]' с точки зрения производительности? Если у меня есть большая таблица, то это вредит производительности моего приложения?

Например, сравните с операцией =, например. WHERE column_name='value', операция REGEXP намного медленнее, чем = для большой таблицы?

Есть еще один ответ, который предложил мне использовать LIKE, но я думаю, что это не хорошо с точки зрения производительности.

Затем я googled и нашел статью в которой говорится, что использование LIKE даже быстрее, чем REGEXP. Я запутался, и я должен использовать для одного запроса соответствия слова в большой таблице...

Могу ли я сказать, что = - самая быстрая операция, тогда LIKE и REGEXP является самой бедной из перспективы производительности?

Ответ 1

Что касается regexp

Регулярное выражение может никогда использовать индекс в MySQL.
= будет использовать индекс, если:

в столбце объявляется индекс;
значения в столбце имеют достаточную мощность (если более чем на +/- 20% строк совпадают, MySQL не будет использовать индекс, потому что в этом случае выполнение полного сканирования таблицы быстрее);
Другие индексы в одной таблице не подходят (MySQL может использовать только один индекс для каждой подсекции);

Учитывая эти и некоторые другие более эзотерические оговорки, сравнение = намного быстрее, чем регулярное выражение.

Относительно

LIKE может использовать индекс, если подстановочный знак не первый char.

SELECT * FROM t WHERE a LIKE 'abc'   <<-- (case insensitive `=`) can use an index
SELECT * FROM t WHERE a LIKE 'abc%'  <<-- can use an index
SELECT * FROM t WHERE a LIKE 'a%'    <<-- can use an index, depending on cardinality
SELECT * FROM t WHERE a LIKE '%a%'   <<-- cannot use an index
SELECT * FROM t WHERE a LIKE '_agf'  <<-- cannot use an index

Производительность like при использовании индекса очень близка к = (при условии, что возвращается столько же строк).

Ответ 2

MySQL не использует индекс с REGEXP. Это основная проблема, хорошая статья связана → http://www.dbasquare.com/2012/03/31/mysql-queries-with-regexp/

Ответ 3

Существует еще один способ поиска данных: Полнотекстовый поиск. Его можно использовать, когда like, = недостаточно (время выполнения), а с другой стороны Sphinx, Lucene слишком мощная.

Чтобы использовать его, вы должны создать полнотекстовый индекс в столбце и запросить его. Если вы его используете, будьте в курсе системных варов ft_min_word_len, ft_max_word_len, которые уменьшают слова min/max size.

Надеюсь, что это поможет.

На оставшийся ваш вопрос ответил @Johan.