mysql optimization - How to create an index using mysql fuzzy query and order by?

Question

For example, there is a mysql statement like this:

select * from city where city_name like '%hf%' order by created_at;

How to create an index for such a sql statement? If you want to create a composite index, use:

key(created_at,city_name)

Since created_at is in front, it degenerates into key(created_at) according to the leftmost matching principle of the index.

key(city_name,created_at)

Definitely not possible. The previous one was a fuzzy query, and this composite index degenerated into key (city). In this case, can only a single index column be created to solve the problem?
Please give me some advice.

Answer 1

% both before and after are not indexed, and there is no index degradation problem. Either change the sql to prefix matching or add a full-text index (not suitable for short strings). Furthermore, for this basically unchanged data, The application can be put into the search engine, and its word segmenter can be used to search