Как оптимизировать поиск телефонного номера (часть строки)?

Question

[Александр]

Кто-нибудь уже натыкался на затык с медленным поиском LIKE "%str%", я уверен. Сейчас в таблице около 300к записей и будет только больше. По этой таблице нужно искать строки по номеру телефона: полностью, начало, конец.
Примеры:

• 7999541...
  
• 79995415742
  
• ...541...
  
• ...41574
  

Все 4 варианта поиска актуальны и нужны. Использую MySQL 5.7.26, столбец с номером хранится в varchar(11), но уже даже сейчас запрос на выборку занимает 1.7 сек. — несколько JOIN'ов, но в целом ситуация плоха из-за LIKE "%str%".

Были идеи на счёт полного перебора всех номеров (РФ) и их частей + хранение хешей этих "запросов на поиск", но дальше идеи не развивается. Возможно ли как-то оптимизировать поиск? Кто применял у себя какие-то плюшки по этому поводу?

Comments

[Александр]

Иван, ограничения нет, но юзеры не вводят менее 3-4 цифр.

Answer 1

[Дмитрий Дерепко]

1. Индекс есть?
2. Открывайте EXPLAIN/DESCRIBE, и ищите проблему.
3. Попробуйте кинуть fultext индекс и искать через MATCH
4. Почему здесь тег PHP?

Comments

[Александр]

1. Да, индекс есть, но он не работает, ведь в запросе есть LIKE "%str%"
   
2. Проблема именно в "тяжёлом" условии, от чего и хотел избавиться
   
3. Занятно, но в доках нет нужного примера-аналога LIKE "%str%"
   
4. Бэк написан на PHP, предполагаю, что можно реализовать на стороне бэка какой-нибудь алгоритм, а потом по результату алгоритма искать в БД
   

[Александр]

Иван, крутяк, думал что не дойду до Sphinx, а найду решение "в коробке", но увы. Спасибо!

[Дмитрий Дерепко]

Александр, Вам Sphinx и не нужен.
www.mysql.ru/docs/man/Fulltext_Search.html
В мускуле всё есть

[Дмитрий Дерепко]

Может быть еще будет прок от встроенных функций, но не пробовал такое извращение.
MATCH по 10м базе работал на ура по 3 колонкам

[Александр]

Дмитрий Дерепко, Поправьте, если я не так прочитал доки:

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

Поиск по первым символам так не будет работать?

[Дмитрий Дерепко]

Черт, да, в Sphinx'е такое можно сделать, а здесь нужно начинать все с самого начала.
А вы пробовали загнать все в integer тип или пробовать LIKE с более строгой маской?
Если вам не принципиально, чтобы пользователь искал с середины, то сделайте маску (Типа +7 (...) ... .. .. ) на клиенте и ищите запросы LIKE 'PHONE%'
Сюда же можно будет прикрутить и MATCH мускульный.
Еще совет - покопайтесь в настройках MySQL сервера и/или докупите железа, если сервер слабый. 300к для LIKE это фигня. Скиньте EXPLAIN

[Александр]

Дмитрий Дерепко, Пока с INT'ом не баловался, но проку будет ноль — ведь конструкция LIKE всё равно ищет строку в указанной колонке, т.е. будет ещё конвертирование инта в строку...
Создание маски уменьшит "удобство" работы, загоняя юзера в рамки тех. процесса. Не хотелось бы.
Банальный пример: нужно быстро найти запись, где в середине телефона мелькают "1111" — ведь юзеру проще запомнить последовательность одинаковых символов, чем вводить номер с начала или полностью (номер может быть недоступен для копирования, н-р под диктовку).

А вот на счёт железа.. Не думаю, что я вообще адекватно использую все ресурсы сервера своими запросами и настройками. Пережил пару холиваров на счёт своей конфигурации, но затык случается только там, где LIKE "%str%".

[Дмитрий Дерепко]

Видимо, придется использовать Sphinx :)

[Александр]

Дмитрий Дерепко, Хе-хе. Ну так даже интереснее. Хотя я думал, что колдовать с самодельными индексами будет лучше. Спасибо вам!

[Александр]

Дмитрий Дерепко, кстати говоря, поможет ли Sphinx, если в условии будет что-то вроде этого:

select * from `handling` as `h`
left join `recall_data` as `rd` on `rd`.`id` = `h`.`recall_id`
where (h.aon REGEXP '07140303' OR rd.phone REGEXP '07140303')

[Дмитрий Дерепко]

Александр, он выступает в роли индексатора и некого помощника в поисках по этим индексам. настроите индексы на нужных колонках, то всё должно нормально работать. и по максимуму предпочитайте left join inner join'у.
Не знаю в чем разница между REGEXP и MATCH. я пользовался только MATCH'ем

[Александр]

Дмитрий Дерепко, Я и хотел узнать, индексируемые колонки в разных таблицах. Средствами MySQL такие индексы я не осилил, а Sphinx сможет?

ЗЫ: REGEXP в данном примере аналог LIKE "%str%"

[Дмитрий Дерепко]

Александр, я думаю, это будет 2 разных независимых индекса

[mr-troll]

Александр, поиск по середине - не то что указано в условии вопроса. Только like, фултекст вам не поможет тут, это индекс отдельных слов. А у вас одно "слово" в столбце.

Answer 2

[Boris Köln]

https://habr.com/ru/post/78566/

Answer 3

[mr-troll]

Во-первых, юзай индекс. Он будет работать при полном совпадении или поиске по началу.
Во-вторых, от like %phone% можно отказаться если у тебя разные поиски. Он ищет любое вхождение. Лучше заменить отдельно на:
`phone` like "000" полное.
`phone` like "000%" по началу .
`phone` like "%000" по концу - делает фулскан, не оптимизируется индексами

Оптимизировать поиск по концу можно добавив столбцы:
1, 2, 3, 4, 5 (сколько надо), цифр с конца, с соответствующими индексами,и уже на уровне приложения искать полным равенством по нужному столбцу