poor query plan: remove uneeded temp/filesort - mrr=on and join_cache_level>2

CREATE TABLE `customers_info` (   `customers_id` bigint(20) unsigned NOT NULL DEFAULT '0',   `account_created_date` datetime DEFAULT NULL, status tinyint NOT NULL;

CREATE TABLE `customers_info` (   `customers_id` bigint(20) unsigned NOT NULL DEFAULT '0',   `account_created_date` datetime DEFAULT NULL, status tinyint NOT NULL,   PRIMARY KEY (`customers_id`),  KEY `status_account_created_date` (status,account_created_date));

insert into customers (customers_id) select * from  seq_1_to_50000;

insert into customers_info select customers_id,NOW() - INTERVAL  customers_id DAY, customers_id MOD 50 from customers; 

analyze table customers_info; analyze table customers;

set optimizer_switch  = 'mrr=on,mrr_sort_keys=on', join_cache_level=8;

explain extended select customers.customers_id,  customers.customers_username,customers_info.account_created_date from customers JOIN customers_info USING(customers_id) WHERE status=30 ORDER BY account_created_date DESC LIMIT 1

+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+--------------------------------------------------------------------+

| id   | select_type | table          | type   | possible_keys                       | key                         | key_len | ref                              | rows | filtered | Extra                                                             |

+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+-------------------------------------------------------------------+

|    1 | SIMPLE      | customers_info | ref    | PRIMARY,status_account_created_date | status_account_created_date | 1       | const                            |  999 |   100.00 | Using index; Using temporary; Using filesort                       |

|    1 | SIMPLE      | customers      | eq_ref | PRIMARY                             | PRIMARY                     | 4       | test.customers_info.customers_id |    1 |   100.00 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan |

+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+-------------------------------------------------------------------+

MariaDB [test]> select @@optimizer_switch, @@join_cache_level\G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on

@@join_cache_level: 2

MariaDB [test]> explain extended select customers.customers_id,  customers.customers_username,customers_info.account_created_date from customers JOIN customers_info USING(customers_id) WHERE status=30 ORDER BY account_created_date DESC LIMIT 1;

+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+--------------------------+

| id   | select_type | table          | type   | possible_keys                       | key                         | key_len | ref                              | rows | filtered | Extra                    |

+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+--------------------------+

|    1 | SIMPLE      | customers_info | ref    | PRIMARY,status_account_created_date | status_account_created_date | 1       | const                            |  999 |   100.00 | Using where; Using index |

|    1 | SIMPLE      | customers      | eq_ref | PRIMARY                             | PRIMARY                     | 4       | test.customers_info.customers_id |    1 |   100.00 | Using where              |

+------+-------------+----------------+--------+-------------------------------------+-----------------------------+---------+----------------------------------+------+----------+--------------------------+