How to optimize the search for a phone number (part of a string)?

Question

PHP

Has anyone run into the slow LIKE "%str%" gag, I'm sure. Now there are about 300k records in the table and there will only be more. According to this table, you need to search for lines by phone number: completely, beginning, end.
Examples:

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

All 4 search options are relevant and necessary. I'm using MySQL 5.7.26, the number column is stored in varchar(11), but even now the fetch request takes 1.7 seconds . - several JOINs, but in general the situation is bad because of LIKE "%str%" .
There were ideas about a complete enumeration of all numbers (RF) and their parts + storage of hashes of these "search requests", but the idea is not developed further. Is it possible to somehow optimize the search? Who used some kind of buns about this?

Answer 1

1. Is there an index?
2. Open EXPLAIN/DESCRIBE and look for the problem.
3. Try throwing a fultext index and searching with MATCH
4. Why is there a PHP tag here?

Answer 2

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

Answer 3

First, use the index. It will work on full match or search at the beginning.
Second, you can opt out of like %phone% if you have different searches. It looks for any occurrence. It is better to replace separately with:
`phone` like "000" complete.
`phone` like "000%" at the beginning .
`phone` like "%000" at the end - does a fullscan, not optimized
by
indexes application level search by full equality on the desired column