Description:
Upgrading from 5.7 to 8.0 required us to add JOIN_FIXED_ORDER query hint for optimizer to respect our STRAIGHT_JOIN. This hint was not required in 5.7 to get the correct query plan.
Even more odd, is that JOIN_FIXED_ORDER is no longer required after adding it to the query 1 time. So, I can't get the old plan to generate any more. Did MySQL learn the new query plan some how?
How to repeat:
Wrong Plan:
+----+-------------+--------------------------+------------+--------+-------------------------------------------+-------------------------------------------+---------+----------------------------------------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------------+------------+--------+-------------------------------------------+-------------------------------------------+---------+----------------------------------------------------------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2595 | 100.00 | NULL |
| 1 | PRIMARY | d_url | NULL | eq_ref | PRIMARY | PRIMARY | 4 | inner_sql.url_key | 1 | 100.00 | NULL |
| 1 | PRIMARY | d_url_path | NULL | eq_ref | PRIMARY | PRIMARY | 4 | lh-warehouse.d_url.url_key | 1 | 100.00 | NULL |
| 2 | DERIVED | d_date | NULL | const | PRIMARY,date_db | PRIMARY | 2 | const | 1 | 100.00 | Using temporary; Using filesort |
| 2 | DERIVED | d_provider | NULL | index | PRIMARY,unq_provider_name | unq_provider_name | 152 | NULL | 21 | 100.00 | Using where; Using index |
| 2 | DERIVED | a_advertiser_stats_daily | NULL | ref | date-provider-campaign-keyword-advertiser | date-provider-campaign-keyword-advertiser | 4 | const,lh-warehouse.d_provider.provider_key | 401 | 100.00 | NULL |
| 2 | DERIVED | d_campaign | NULL | eq_ref | PRIMARY,idx_d_campaign_lookup | PRIMARY | 3 | lh-warehouse.a_advertiser_stats_daily.campaign_key | 1 | 43.32 | Using where |
| 2 | DERIVED | d_advertiser | NULL | eq_ref | PRIMARY,idx_d_advertiser_lookup | PRIMARY | 3 | lh-warehouse.a_advertiser_stats_daily.advertiser_key | 1 | 100.00 | Using where |
| 2 | DERIVED | f_keyword_rank | p202402 | ref | date_key-campaign_key | date_key-campaign_key | 5 | const,lh-warehouse.a_advertiser_stats_daily.campaign_key | 710 | 1.00 | Using where |
| 2 | DERIVED | d_ad_attributes | NULL | eq_ref | PRIMARY | PRIMARY | 3 | lh-warehouse.f_keyword_rank.ad_attributes_key | 1 | 100.00 | NULL |
| 2 | DERIVED | d_keyword | NULL | eq_ref | PRIMARY | PRIMARY | 3 | lh-warehouse.a_advertiser_stats_daily.keyword_key | 1 | 100.00 | NULL |
| 2 | DERIVED | d_ad | NULL | eq_ref | PRIMARY | PRIMARY | 4 | lh-warehouse.f_keyword_rank.ad_key | 1 | 10.00 | Using where |
| 2 | DERIVED | d_date | NULL | eq_ref | PRIMARY | PRIMARY | 2 | lh-warehouse.d_ad.ad_first_seen_date_key | 1 | 100.00 | NULL |
+----+-------------+--------------------------+------------+--------+-------------------------------------------+-------------------------------------------+---------+----------------------------------------------------------+------+----------+---------------------------------+
Correct Plan:
+----+-------------+--------------------------+------------+--------+-------------------------------------------+-------------------------------------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------------+------------+--------+-------------------------------------------+-------------------------------------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 163762 | 100.00 | NULL |
| 1 | PRIMARY | d_url | NULL | eq_ref | PRIMARY | PRIMARY | 4 | inner_sql.url_key | 1 | 100.00 | NULL |
| 1 | PRIMARY | d_url_path | NULL | eq_ref | PRIMARY | PRIMARY | 4 | lh-warehouse.d_url.url_key | 1 | 100.00 | NULL |
| 2 | DERIVED | d_date | NULL | const | PRIMARY,date_db | PRIMARY | 2 | const | 1 | 100.00 | Using temporary; Using filesort |
| 2 | DERIVED | d_campaign | NULL | ALL | PRIMARY,idx_d_campaign_lookup | NULL | NULL | NULL | 644 | 43.32 | Using where |
| 2 | DERIVED | f_keyword_rank | p202402 | ref | date_key-campaign_key | date_key-campaign_key | 5 | const,lh-warehouse.d_campaign.campaign_key | 586 | 100.00 | Using where |
| 2 | DERIVED | d_ad_attributes | NULL | eq_ref | PRIMARY | PRIMARY | 3 | lh-warehouse.f_keyword_rank.ad_attributes_key | 1 | 100.00 | NULL |
| 2 | DERIVED | d_keyword | NULL | eq_ref | PRIMARY | PRIMARY | 3 | lh-warehouse.f_keyword_rank.keyword_key | 1 | 100.00 | NULL |
| 2 | DERIVED | d_advertiser | NULL | eq_ref | PRIMARY,idx_d_advertiser_lookup | PRIMARY | 3 | lh-warehouse.f_keyword_rank.advertiser_key | 1 | 100.00 | Using where |
| 2 | DERIVED | d_ad | NULL | eq_ref | PRIMARY | PRIMARY | 4 | lh-warehouse.f_keyword_rank.ad_key | 1 | 100.00 | NULL |
| 2 | DERIVED | d_provider | NULL | eq_ref | PRIMARY,unq_provider_name | unq_provider_name | 152 | func | 1 | 100.00 | Using where; Using index |
| 2 | DERIVED | d_date | NULL | eq_ref | PRIMARY | PRIMARY | 2 | lh-warehouse.d_ad.ad_first_seen_date_key | 1 | 100.00 | NULL |
| 2 | DERIVED | a_advertiser_stats_daily | NULL | eq_ref | date-provider-campaign-keyword-advertiser | date-provider-campaign-keyword-advertiser | 13 | const,lh-warehouse.d_provider.provider_key,lh-warehouse.d_campaign.campaign_key,lh-warehouse.f_keyword_rank.keyword_key,lh-warehouse.f_keyword_rank.advertiser_key | 1 | 100.00 | NULL |
+----+-------------+--------------------------+------------+--------+-------------------------------------------+-------------------------------------------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+----------+---------------------------------+
