На сколько оптимально искать выражение по строке в MySQL?

Question

[Vitaliy]

Вообще говоря для бота нужно хранить некое постоянно возрастающее количество шаблонов к сообщениям, по которым и искать нужный ответ.
Например, ^привет!?$; ^как дела .*$
Сперва все это помещалось в коде, но это до ужаса не гибко при возрастающем количестве сообщений, на которые бот должен уметь отвечать. Поэтому было принято решение поместить шаблоны с ответами(а вернее с инструкциями для ответа) в таблицу в MySQL, что имеет следующий вид.

Тогда по входящему сообщению находить шаблон

SELECT * FROM test WHERE 'привет' regexp reg;

на сколько это рационально с точки зрения производительности? Как MySQL подбирает шаблон по сообщения в случае индексированного поля?

Answer 1

[Uber Noob]

Имхо надо хранить в полях типа text и искать полнотекстовым поиском по нему (по varchar вроде нет полнотекстового)

Comments

[Vitaliy]

разве тогда через REGEXP не будет перебирать все записи?

[Сергей Протько]

Виталий Столяров: искать полнотекстовым поиском а не регулярками.

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

[Uber Noob]

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

[Vitaliy]

Сергей Протько: пока ничего кроме как вынести первое слово шаблона (которое должно быть фиксированным) в поле для LIKE а дальше из подходящих все перебирать по регулярке. А что там на счет построения индекса, в какую сторону "копать"?

Answer 2

[Сергей Протько]

dev.mysql.com/doc/refman/5.7/en/regexp.html

на сколько это рационально с точки зрения производительности?

поскольку индексы использоваться не будут - рационально только на очень маленьких выборках (до 1000 записей например). В вашем случае вам даже регулярки не нужны - достаточно обычного matching/like.

В идеале для подобных задач стоит использовать вещи вроде Elastic Search. У MySQL хоть и есть поддержка full-text search и весьма неплохая, но далеко не такая же как у эластики.

Comments

[Vitaliy]

для этих примеров не нужны, но для более сложных примеров где слова в середине сообщения могут быть произвольными, и их попросту необходимо только по регулярным искать

[Uber Noob]

Виталий Столяров:

где слова в середине сообщения

Полнотекстовый поиск прекрасно их найдёт.

[Vitaliy]

ubernoob: а потом из найденного повторно проверять уже полноценно по regexp? То есть без первоначального отбора перед регулярным никак не обойтись?

[Uber Noob]

Виталий Столяров: регулярки это вообще одна из самых ресурсозатратных штук, например если не брать базу, то в простом PHP preg_replace в 3 раза медленнее str_replace

[Vitaliy]

ubernoob: спасибо, я в курсе, но вот от этого аналога регуляркам не найду

[Сергей Протько]

Виталий Столяров: приведите текст в нормальный вид что бы не пришлось регулярками проходить и все будет хорошо.

[Vitaliy]

Сергей Протько: и как, например, следующее разобрать без регулярок?
--
Сколько в <название танка> урона на <номер> орудии ?
--
Кажется, никак.

[Сергей Протько]

Виталий Столяров: у меня простой вопрос - почему бы не прогнать через регулярки ПЕРЕД вставкой в таблицу и держать все это рядом например в json (mysql его умеет с версии 5.7)

[Vitaliy]

Сергей Протько: в таблицу ничего не требуется вставлять, нужно лишь получать шаблоны которые соответствуют входящему сообщению