mysql index selection logic?

Question

MySQL

I have a posts table with the following indexes:

PRIMARY	id
UNIQUE	post_url
UNIQUE	fb_id
UNIQUE	tw_id
UNIQUE	yt_video_id
UNIQUE	bit_id
UNIQUE	ticketfly_event_id
UNIQUE	entity_id, amazon_asin
UNIQUE	itunes_collection_id, entity_id
UNIQUE	soundcloud_id
UNIQUE	event_id
UNIQUE	media_id, entity_id
UNIQUE	entity_id, festival_id
INDEX	itunes_release_date
INDEX	amazon_release_date
INDEX	entity_id, post_type, is_duplicate, deleted
INDEX	google_country

And there is this query:

explain 

select `bq_posts`.* 
from bq_posts 
where (`entity_id` in ('93', '146', '191', '909', '946', '1444', '1686', '2102', '2129', '2147', '2213', '2236', '2532', '2553', '2591', '3119', '3175', '3283', '4742', '4825', '4906', '5095', '5556' /*Еще несколько тысяч идентификаторов*/))
and `post_type` in ('facebook', 'twitter', 'youtube', 'vevo', 'itunes', 'amazon', 'soundcloud', 'bit', 'ticketfly', 'festival', 'event', 'media')
and `bq_posts`.`deleted` is null 
and `bq_posts`.`is_duplicate` = '0'  
limit 10 offset 0;

That is, the entity_id, post_type, deleted and is_duplicate fields participate in the condition. According to my logic, the index entity_id, post_type, is_duplicate, deleted is ideal here , but explain shows that entity_id, amazon_asin is used , which is UNIQUE. Moreover, the choice of index depends on the number of identifiers in the query. There are several thousand of them, but if I reduce them to 10-20, then explain shows that the required index is used ( entity_id, post_type, is_duplicate, deleted ).
And it all started when moving from mysql 5.7 to percona server 5.7. Before the move, this request was instantaneous, but now it has begun to slow down.
What could be wrong?

Answer 1

And it all started when moving from mysql 5.7 to percona server 5.7. Before the move, this request was instantaneous, but now it has begun to slow down.

Sorry for the sarcasm, "but this is MySQL, what did you expect?". One of the features of this database is a rather "clumsy" query optimizer (if you can call it that), which can not always correctly determine which index should be used. Of course, the incorrect definition of the "optimal" index is a problem not only for MySQL, but also for other databases ... but, somehow, very weakly in MySQL (and its derivatives) they try to deal with this. Also exacerbating the problem is the fact that MySQL can only use 1 index per query + some other factors.
"What to do with it?" - it's hard to say, it's unlikely that in the foreseeable future, you (or the developers of MySQL or other databases) will be able to solve this problem entirely, but specifically in your case, from personal experience, I can assume that the most optimal solution would be to "poke a finger, which use the index" well, and track / debug / correct similar queries in the future.
Also, I want to draw your attention to the fact that the problem here is most likely not in "Percona" as such, and MySQL and MariaDB, etc., often sin with such things, with relatively large amounts of data.