Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
None
-
None
Description
After update MariaDB from 10.1.15 to 10.3.7 optimizer_switch derived_with_keys does not work.
explain select * from view; |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------+---------+-----------------------+---------------+-------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------+---------+-----------------------+---------------+-------------------------------------------------+ |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2662732242432 | | |
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 158208 | Using temporary; Using filesort | |
| 2 | DERIVED | oh | ALL | orderRelIDX_2002,ars_report_idx_orderhistoryentries_3,ars_report_idx_orderhistoryentries_prevstatus | NULL | NULL | NULL | 33661158 | Using where; Using join buffer (flat, BNL join) | |
| 2 | DERIVED | o | ref | PRIMARY | PRIMARY | 8 | hybrisdb.oh.p_order | 1 | Using index | |
| 4 | DERIVED | ugl | ALL | PRIMARY,ars_report_idx_usergroupslp_itempk_langpk | NULL | NULL | NULL | 64 | Using where; Using temporary; Using filesort | |
| 4 | DERIVED | pgr | ref | linksource_201,linktarget_201 | linktarget_201 | 9 | hybrisdb.ugl.ITEMPK | 3296 | Using where | |
| 4 | DERIVED | u | eq_ref | PRIMARY | PRIMARY | 8 | hybrisdb.pgr.SourcePK | 1 | | |
| 5 | SUBQUERY | l | ref | ISOCode_32 | ISOCode_32 | 768 | const | 1 | Using where; Using index | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------+---------+-----------------------+---------------+-------------------------------------------------+ |
|
|
set optimizer_switch='derived_with_keys=on' |
|
|
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------+---------+-----------------------+---------------+-------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------+---------+-----------------------+---------------+-------------------------------------------------+ |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2662732084224 | | |
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 158208 | Using temporary; Using filesort | |
| 2 | DERIVED | oh | ALL | orderRelIDX_2002,ars_report_idx_orderhistoryentries_3,ars_report_idx_orderhistoryentries_prevstatus | NULL | NULL | NULL | 33661157 | Using where; Using join buffer (flat, BNL join) | |
| 2 | DERIVED | o | ref | PRIMARY | PRIMARY | 8 | hybrisdb.oh.p_order | 1 | Using index | |
| 4 | DERIVED | ugl | ALL | PRIMARY,ars_report_idx_usergroupslp_itempk_langpk | NULL | NULL | NULL | 64 | Using where; Using temporary; Using filesort | |
| 4 | DERIVED | pgr | ref | linksource_201,linktarget_201 | linktarget_201 | 9 | hybrisdb.ugl.ITEMPK | 3296 | Using where | |
| 4 | DERIVED | u | eq_ref | PRIMARY | PRIMARY | 8 | hybrisdb.pgr.SourcePK | 1 | | |
| 5 | SUBQUERY | l | ref | ISOCode_32 | ISOCode_32 | 768 | const | 1 | Using where; Using index | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+----------------+---------+-----------------------+---------------+-------------------------------------------------+ |
On 10.1.15 save explain:
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+------------------------------+---------+------------------------+-----------+-------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+------------------------------+---------+------------------------+-----------+-------------------------------------------+ |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 108830260 | | |
| 2 | DERIVED | o | index | PRIMARY | ars_report_idx_orders_create | 8 | NULL | 5441513 | Using where; Using index; Using temporary | |
| 2 | DERIVED | oh | ref | orderRelIDX_2002,ars_report_idx_orderhistoryentries_3,ars_report_idx_orderhistoryentries_prevstatus | orderRelIDX_2002 | 9 | hybrisdb.o.PK | 2 | Using where | |
| 2 | DERIVED | <derived4> | ref | key0 | key0 | 8 | hybrisdb.oh.p_employee | 10 | |
|
| 4 | DERIVED | ugl | ALL | PRIMARY,ars_report_idx_usergroupslp_itempk_langpk | NULL | NULL | NULL | 64 | Using where; Using temporary | |
| 4 | DERIVED | pgr | ref | linksource_201,linktarget_201 | linktarget_201 | 9 | hybrisdb.ugl.ITEMPK | 107570 | Using where | |
| 4 | DERIVED | u | eq_ref | PRIMARY | PRIMARY | 8 | hybrisdb.pgr.SourcePK | 1 | | |
| 5 | SUBQUERY | l | ref | ISOCode_32 | ISOCode_32 | 768 | const | 1 | Using where; Using index | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+------------------------------+---------+------------------------+-----------+-------------------------------------------+ |
|
|
|
|
set optimizer_switch='derived_with_keys=off' |
explain select * from ars_report_full_order_status_history; |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+------------------------------+---------+-----------------------+----------------+-------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+------------------------------+---------+-----------------------+----------------+-------------------------------------------------+ |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 56193105048000 | | |
| 2 | DERIVED | o | index | PRIMARY | ars_report_idx_orders_create | 8 | NULL | 5441525 | Using where; Using index; Using temporary | |
| 2 | DERIVED | oh | ref | orderRelIDX_2002,ars_report_idx_orderhistoryentries_3,ars_report_idx_orderhistoryentries_prevstatus | orderRelIDX_2002 | 9 | hybrisdb.o.PK | 2 | Using where | |
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 5163360 | Using where; Using join buffer (flat, BNL join) | |
| 4 | DERIVED | ugl | ALL | PRIMARY,ars_report_idx_usergroupslp_itempk_langpk | NULL | NULL | NULL | 64 | Using where; Using temporary | |
| 4 | DERIVED | pgr | ref | linksource_201,linktarget_201 | linktarget_201 | 9 | hybrisdb.ugl.ITEMPK | 107570 | Using where | |
| 4 | DERIVED | u | eq_ref | PRIMARY | PRIMARY | 8 | hybrisdb.pgr.SourcePK | 1 | | |
| 5 | SUBQUERY | l | ref | ISOCode_32 | ISOCode_32 | 768 | const | 1 | Using where; Using index | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+------------------------------+---------+-----------------------+----------------+-------------------------------------------------+ |