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
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;
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.
Find more questions by tags MySQL