The logic of a mysql index?

There is a posts table with those 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 here such request:
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' /*Thousands of IDs*/))
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, in the condition involved field entity_id, post_type, deleted and is_duplicate. My logic here is perfect index entity_id, post_type, is_duplicate, deleted, but explain shows that it uses entity_id, amazon_asin, which is UNIQUE. Moreover, the choice of index depends on the number of identification in the query. There are several thousands, but if I reduce to 10-20, then the explain shows that it uses the correct index (entity_id, post_type, is_duplicate, deleted).
And it all started when moving from mysql to percona server 5.7 5.7. Before moving this request was instant, and now began to slow down.
What could be wrong?
June 14th 19 at 20:58
1 answer
June 14th 19 at 21:00
And it all started when moving from mysql to percona server 5.7 5.7. Before moving this request was instant, and now began to slow down.
Sorry for the sarcasm, "but it's MySQL, what did you expect?". One of the features of this database is fairly straightforward, the query optimizer (if you can call it that), which is not always correct can determine which index to use. Of course, not the correctness of the definition of "optimal" index is not just a problem of MySQL, but other database... but somehow very little in MySQL (and its derivatives) are trying to deal with it. The problem is compounded by the fact that MySQL can use only 1 index to query + some other factors.

"What to do with it?" - hard to say, it is unlikely in the foreseeable future, You (or developers MySQL or other DB) will be able to solve the problem entirely, but specifically in Your case, from personal experience, I can assume that the best solution would be to"poke a finger which index to use" well to track/debug/adjust these queries in the future.

Also, I want to draw Your attention that the problem here is most likely not "Percona" as such, and MySQL and MariaDB, etc., not rarely err things like that, when relatively large amounts of data.
All of a sudden that rented a more powerful server and also updated everything, including the changed database percona and had problems with speed where there were no problems. - rupert_Weissnat79 commented on June 14th 19 at 21:03
here... very sensitive issue .. I have many different "MySQL-variations" have tried, with varying volume data, etc., it is very difficult to give unequivocal advice from the "do this and all will be well" in some cases, Percona may behave more "appropriately" than the original MySQL, and sometimes Vice versa... That would give a full recommendation on the need to perform the amount of data, the settings of the old database and the new how it all built in, etc to fully answer the question "on what basis MySQL chooses the index" is quite difficult because it is affected by many factors and in the end, the DB engines people are doing and nobody is insured from banal bugs... I think just to force the database to use the right ("right") the index in the queries that You have began to slow down - the fastest and most reliable solution. - Salvado commented on June 14th 19 at 21:06

Find more questions by tags MySQL