What is the best way to organize autocomplete search in MySQL databases?

Question

MySQL

There is an end-to-end search form for a web application (PHP+MariaDB).
There are approximately 15 SQL tables that are searched:
- customers
- products
- employees
, etc.
Tables have text fields like "name", "model", "description", etc.
Now the search is performed through a query like LIKE '%string%', so indexes are not used, and the search is slow.
How to speed it up?
- Organize a Fulltext search on existing tables (simply sorting through all 15 in turn) via a MATCH AGAINST query? The problem is that FULLTEXT builds indexes only at the beginning of a word, and ideally I would like to search in the middle of a word too. For example, the phone number in the database is 79112222222, and the user can try to find it by 9112222222 or by 2222222 in general. LIKE '%222222% ' is more appropriate. Likewise with the name of the model. Plus FULLTEXT is not friendly with other indexes. For example, if you need to search not for all products, but only for products of a certain category, the category index is not used in the query.
- Make a separate table with texts and search on it?
- Use a third party solution like ElasticSearch?
In the latter case, I would really like real-time synchronization with MySQL: if Masha's client was added, she needs to appear in the search immediately.
Or is there some other solution?

Answer 1

The last option is correct. elastisearch or sphinxsearch. Real-time synchronization will have to be done in your application (write a new object first in the database, and then in es|ss).