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 | |
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+------------------------------+---------+-----------------------+----------------+-------------------------------------------------+ |
Attachments
Activity
main.cnf the same.
CREATE VIEW `ars_report_full_order_status_history` AS select max(`oh`.`p_order`) AS `order_id`,max(`oh`.`createdTS`) AS `creation_date`,max(`oh`.`modifiedTS`) AS `modification_date`,group_concat(`oh`.`p_description`,'' separator ',') AS `description`,max(`oh`.`p_prevstatus`) AS `previous_status_id`,max(`oh`.`p_orderstatus`) AS `current_status_id`,max(`oh`.`p_employee`) AS `employee_id`,max(`u`.`user_id`) AS `user_id`,max(`u`.`user_name`) AS `user_name`,max(`u`.`user_roles`) AS `user_roles` from ((`ars_report_full_order_status_orders` `o` join `orderhistoryentries` `oh` on(`o`.`PK` = `oh`.`p_order`)) join `ars_report_full_order_status_user_groups` `u` on(`oh`.`p_employee` = `u`.`user_id`)) where (`oh`.`p_orderstatus` is not null or `oh`.`p_prevstatus` is not null) and `oh`.`p_order` is not null group by `oh`.`p_order`,`oh`.`p_prevstatus`,`oh`.`p_orderstatus` order by NULL
Second view:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ars_report_full_order_status_orders` AS select `o`.`hjmpTS` AS `hjmpTS`,`o`.`createdTS` AS `createdTS`,`o`.`modifiedTS` AS `modifiedTS`,`o`.`TypePkString` AS `TypePkString`,`o`.`OwnerPkString` AS `OwnerPkString`,`o`.`PK` AS `PK`,`o`.`calculatedflag` AS `calculatedflag`,`o`.`code` AS `code`,`o`.`currencypk` AS `currencypk`,`o`.`deliveryaddresspk` AS `deliveryaddresspk`,`o`.`deliverycost` AS `deliverycost`,`o`.`deliverymodepk` AS `deliverymodepk`,`o`.`deliverystatuspk` AS `deliverystatuspk`,`o`.`globaldiscountvalues` AS `globaldiscountvalues`,`o`.`netflag` AS `netflag`,`o`.`paymentaddresspk` AS `paymentaddresspk`,`o`.`paymentcost` AS `paymentcost`,`o`.`paymentinfopk` AS `paymentinfopk`,`o`.`paymentmodepk` AS `paymentmodepk`,`o`.`paymentstatuspk` AS `paymentstatuspk`,`o`.`statuspk` AS `statuspk`,`o`.`p_exportstatus` AS `p_exportstatus`,`o`.`statusinfo` AS `statusinfo`,`o`.`totalprice` AS `totalprice`,`o`.`totaldiscounts` AS `totaldiscounts`,`o`.`totaltax` AS `totaltax`,`o`.`totaltaxvalues` AS `totaltaxvalues`,`o`.`userpk` AS `userpk`,`o`.`subtotal` AS `subtotal`,`o`.`discountondelivery` AS `discountondelivery`,`o`.`discountonpayment` AS `discountonpayment`,`o`.`p_previousdeliverymode` AS `p_previousdeliverymode`,`o`.`p_site` AS `p_site`,`o`.`p_store` AS `p_store`,`o`.`p_guid` AS `p_guid`,`o`.`p_yoyosum` AS `p_yoyosum`,`o`.`p_transportcompany` AS `p_transportcompany`,`o`.`p_freevouchers` AS `p_freevouchers`,`o`.`p_pointofservice` AS `p_pointofservice`,`o`.`p_deliveryzonelist` AS `p_deliveryzonelist`,`o`.`p_tariffzonelist` AS `p_tariffzonelist`,`o`.`p_ordersubstatus` AS `p_ordersubstatus`,`o`.`p_deletelines` AS `p_deletelines`,`o`.`p_weight` AS `p_weight`,`o`.`p_volume` AS `p_volume`,`o`.`p_weightbulky` AS `p_weightbulky`,`o`.`p_volumebulky` AS `p_volumebulky`,`o`.`p_paywithgift` AS `p_paywithgift`,`o`.`p_paywithyoyo` AS `p_paywithyoyo`,`o`.`p_deletedfreegoodsmap` AS `p_deletedfreegoodsmap`,`o`.`p_versionid` AS `p_versionid`,`o`.`p_originalversion` AS `p_originalversion`,`o`.`p_fraudulent` AS `p_fraudulent`,`o`.`p_potentiallyfraudulent` AS `p_potentiallyfraudulent`,`o`.`p_salesapplication` AS `p_salesapplication`,`o`.`p_language` AS `p_language`,`o`.`p_deliverydate` AS `p_deliverydate`,`o`.`p_deliverytime` AS `p_deliverytime`,`o`.`p_shippingdate` AS `p_shippingdate`,`o`.`p_informingmethod` AS `p_informingmethod`,`o`.`p_requesteddeliverydate` AS `p_requesteddeliverydate`,`o`.`p_sendsmswhenstatuschanged` AS `p_sendsmswhenstatuschanged`,`o`.`p_sapapprovedate` AS `p_sapapprovedate`,`o`.`p_sapcalculatedate` AS `p_sapcalculatedate`,`o`.`p_sapreservedate` AS `p_sapreservedate`,`o`.`p_ordertype` AS `p_ordertype`,`o`.`p_promotionsdate` AS `p_promotionsdate`,`o`.`p_operator` AS `p_operator`,`o`.`p_buyerpaymentresulterror` AS `p_buyerpaymentresulterror`,`o`.`p_parentorder` AS `p_parentorder`,`o`.`p_cslockdate` AS `p_cslockdate`,`o`.`p_cslockoperator` AS `p_cslockoperator`,`o`.`p_ismigrated` AS `p_ismigrated`,`o`.`p_partlypaidsum` AS `p_partlypaidsum`,`o`.`p_deliveryinterval` AS `p_deliveryinterval`,`o`.`p_refunddate` AS `p_refunddate`,`o`.`p_yoyoawardedpoints` AS `p_yoyoawardedpoints`,`o`.`p_yoyodebitedpoints` AS `p_yoyodebitedpoints`,`o`.`p_needrefundyoyo` AS `p_needrefundyoyo`,`o`.`p_giftusedforyoyoaddpoints` AS `p_giftusedforyoyoaddpoints`,`o`.`p_processedtime` AS `p_processedtime`,`o`.`aCLTS` AS `aCLTS`,`o`.`propTS` AS `propTS`,`o`.`p_fulfilmentstatus` AS `p_fulfilmentstatus`,`o`.`p_notes` AS `p_notes`,`o`.`p_computerid` AS `p_computerid`,`o`.`p_ordercreator` AS `p_ordercreator`,`o`.`p_fiascode` AS `p_fiascode`,`o`.`originaldeliverycost` AS `originaldeliverycost`,`o`.`p_ordernotificationstatus` AS `p_ordernotificationstatus`,`o`.`p_chequeassigned` AS `p_chequeassigned`,`o`.`refundeddeliverycost` AS `refundeddeliverycost`,`o`.`p_remindsmscount` AS `p_remindsmscount`,`o`.`p_checkoutsuccess` AS `p_checkoutsuccess`,`o`.`p_modified` AS `p_modified`,`o`.`p_dateofstatusupdate` AS `p_dateofstatusupdate`,`o`.`p_instorecompleted` AS `p_instorecompleted` from `orders` `o`
Third view:
CREATE VIEW `ars_report_full_order_status_user_groups` AS select `u`.`PK` AS `user_id`,max(`u`.`name`) AS `user_name`,group_concat(`ugl`.`p_locname` separator '; ') AS `user_roles` from ((`users` `u` join `pgrels` `pgr` on(`u`.`PK` = `pgr`.`SourcePK`)) join `usergroupslp` `ugl` on(`ugl`.`ITEMPK` = `pgr`.`TargetPK` and `ugl`.`LANGPK` = (select `ars_report_ru_lang_pk`.`pk` from `ars_report_ru_lang_pk`))) group by `u`.`PK` order by NULL
Fourth view:
CREATE VIEW `ars_report_ru_lang_pk` AS select `l`.`PK` AS `pk` from `languages` `l` where `l`.`isocode` = 'ru'
Tables order:
CREATE TABLE `users` (
`hjmpTS` bigint(20) DEFAULT NULL,
`createdTS` datetime(6) DEFAULT NULL,
`modifiedTS` datetime(6) DEFAULT NULL,
`TypePkString` bigint(20) DEFAULT NULL,
`OwnerPkString` bigint(20) DEFAULT NULL,
`PK` bigint(20) NOT NULL DEFAULT 0,
`description` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`uniqueid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_profilepicture` bigint(20) DEFAULT NULL,
`p_ldapsearchbase` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_dn` text COLLATE utf8_bin DEFAULT NULL,
`p_cn` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`defaultpaymentaddress` bigint(20) DEFAULT NULL,
`defaultshippingaddress` bigint(20) DEFAULT NULL,
`passwd` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`encode` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_passwordanswer` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_passwordquestion` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_sessionlanguage` bigint(20) DEFAULT NULL,
`p_sessioncurrency` bigint(20) DEFAULT NULL,
`p_logindisabled` tinyint(1) DEFAULT NULL,
`p_lastlogin` datetime(6) DEFAULT NULL,
`p_hmclogindisabled` tinyint(1) DEFAULT NULL,
`p_userprofile` bigint(20) DEFAULT NULL,
`p_europe1pricefactory_udg` bigint(20) DEFAULT NULL,
`p_europe1pricefactory_upg` bigint(20) DEFAULT NULL,
`p_europe1pricefactory_utg` bigint(20) DEFAULT NULL,
`p_ldapaccount` tinyint(1) DEFAULT NULL,
`p_domain` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_ldaplogin` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_authorizedtounlockpages` tinyint(1) DEFAULT NULL,
`p_yoyocardinfo` bigint(20) DEFAULT NULL,
`aCLTS` bigint(20) DEFAULT 0,
`propTS` bigint(20) DEFAULT 0,
`p_pointofservice` bigint(20) DEFAULT NULL,
`p_contactemail` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_employeeid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_emails` longblob DEFAULT NULL,
`p_regionalmanager` bigint(20) DEFAULT NULL,
`p_innerregion` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_sectortdm` bigint(20) DEFAULT NULL,
`p_customerid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_previewcatalogversions` text COLLATE utf8_bin DEFAULT NULL,
`p_title` bigint(20) DEFAULT NULL,
`p_defaultpaymentinfo` bigint(20) DEFAULT NULL,
`p_token` text COLLATE utf8_bin DEFAULT NULL,
`p_originaluid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_type` bigint(20) DEFAULT NULL,
`p_temppassword` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_region` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_stores` text COLLATE utf8_bin DEFAULT NULL,
`p_middlename` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`PK`),
UNIQUE KEY `UID_4` (`uniqueid`),
KEY `regionalmanagerRelIDX_4` (`p_regionalmanager`),
KEY `sectortdmRelIDX_4` (`p_sectortdm`),
KEY `ars_cc_users_name` (`name`),
KEY `ars_cc_users_name_uniqueid` (`name`,`uniqueid`),
KEY `ars_rest_v1_i_order_defaultshippingaddress` (`defaultshippingaddress`)
CREATE TABLE `languages` (
`hjmpTS` bigint(20) DEFAULT NULL,
`createdTS` datetime(6) DEFAULT NULL,
`modifiedTS` datetime(6) DEFAULT NULL,
`TypePkString` bigint(20) DEFAULT NULL,
`OwnerPkString` bigint(20) DEFAULT NULL,
`PK` bigint(20) NOT NULL DEFAULT 0,
`activeflag` tinyint(1) DEFAULT NULL,
`isocode` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`aCLTS` bigint(20) DEFAULT 0,
`propTS` bigint(20) DEFAULT 0,
PRIMARY KEY (`PK`),
KEY `ISOCode_32` (`isocode`)
CREATE TABLE `orders` (
`hjmpTS` bigint(20) DEFAULT NULL,
`createdTS` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
`modifiedTS` datetime(6) DEFAULT NULL,
`TypePkString` bigint(20) DEFAULT NULL,
`OwnerPkString` bigint(20) DEFAULT NULL,
`PK` bigint(20) NOT NULL DEFAULT 0,
`calculatedflag` tinyint(1) DEFAULT NULL,
`code` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`currencypk` bigint(20) DEFAULT NULL,
`deliveryaddresspk` bigint(20) DEFAULT NULL,
`deliverycost` decimal(30,8) DEFAULT NULL,
`deliverymodepk` bigint(20) DEFAULT NULL,
`deliverystatuspk` bigint(20) DEFAULT NULL,
`globaldiscountvalues` text COLLATE utf8_bin DEFAULT NULL,
`netflag` tinyint(1) DEFAULT NULL,
`paymentaddresspk` bigint(20) DEFAULT NULL,
`paymentcost` decimal(30,8) DEFAULT NULL,
`paymentinfopk` bigint(20) DEFAULT NULL,
`paymentmodepk` bigint(20) DEFAULT NULL,
`paymentstatuspk` bigint(20) DEFAULT NULL,
`statuspk` bigint(20) DEFAULT NULL,
`p_exportstatus` bigint(20) DEFAULT NULL,
`statusinfo` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`totalprice` decimal(30,8) DEFAULT NULL,
`totaldiscounts` decimal(30,8) DEFAULT NULL,
`totaltax` decimal(30,8) DEFAULT NULL,
`totaltaxvalues` text COLLATE utf8_bin DEFAULT NULL,
`userpk` bigint(20) DEFAULT NULL,
`subtotal` decimal(30,8) DEFAULT NULL,
`discountondelivery` tinyint(1) DEFAULT NULL,
`discountonpayment` tinyint(1) DEFAULT NULL,
`p_previousdeliverymode` bigint(20) DEFAULT NULL,
`p_site` bigint(20) DEFAULT NULL,
`p_store` bigint(20) DEFAULT NULL,
`p_guid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_yoyosum` bigint(20) DEFAULT NULL,
`p_transportcompany` bigint(20) DEFAULT NULL,
`p_freevouchers` longblob DEFAULT NULL,
`p_pointofservice` bigint(20) DEFAULT NULL,
`p_deliveryzonelist` text COLLATE utf8_bin DEFAULT NULL,
`p_tariffzonelist` text COLLATE utf8_bin DEFAULT NULL,
`p_ordersubstatus` bigint(20) DEFAULT NULL,
`p_deletelines` longblob DEFAULT NULL,
`p_weight` decimal(30,8) DEFAULT NULL,
`p_volume` decimal(30,8) DEFAULT NULL,
`p_weightbulky` decimal(30,8) DEFAULT NULL,
`p_volumebulky` decimal(30,8) DEFAULT NULL,
`p_paywithgift` tinyint(1) DEFAULT NULL,
`p_paywithyoyo` tinyint(1) DEFAULT NULL,
`p_deletedfreegoodsmap` longblob DEFAULT NULL,
`p_versionid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_originalversion` bigint(20) DEFAULT NULL,
`p_fraudulent` tinyint(1) DEFAULT NULL,
`p_potentiallyfraudulent` tinyint(1) DEFAULT NULL,
`p_salesapplication` bigint(20) DEFAULT NULL,
`p_language` bigint(20) DEFAULT NULL,
`p_deliverydate` datetime(6) DEFAULT NULL,
`p_deliverytime` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_shippingdate` datetime(6) DEFAULT NULL,
`p_informingmethod` bigint(20) DEFAULT NULL,
`p_requesteddeliverydate` datetime(6) DEFAULT NULL,
`p_sendsmswhenstatuschanged` tinyint(1) DEFAULT NULL,
`p_sapapprovedate` datetime(6) DEFAULT NULL,
`p_sapcalculatedate` datetime(6) DEFAULT NULL,
`p_sapreservedate` datetime(6) DEFAULT NULL,
`p_ordertype` bigint(20) DEFAULT NULL,
`p_promotionsdate` datetime(6) DEFAULT NULL,
`p_operator` bigint(20) DEFAULT NULL,
`p_buyerpaymentresulterror` tinyint(1) DEFAULT NULL,
`p_parentorder` bigint(20) DEFAULT NULL,
`p_cslockdate` datetime(6) DEFAULT NULL,
`p_cslockoperator` bigint(20) DEFAULT NULL,
`p_ismigrated` tinyint(1) DEFAULT NULL,
`p_partlypaidsum` decimal(30,8) DEFAULT NULL,
`p_deliveryinterval` bigint(20) DEFAULT NULL,
`p_refunddate` datetime(6) DEFAULT NULL,
`p_yoyoawardedpoints` decimal(30,8) DEFAULT NULL,
`p_yoyodebitedpoints` decimal(30,8) DEFAULT NULL,
`p_needrefundyoyo` tinyint(1) DEFAULT NULL,
`p_giftusedforyoyoaddpoints` decimal(30,8) DEFAULT NULL,
`p_processedtime` datetime(6) DEFAULT NULL,
`aCLTS` bigint(20) DEFAULT 0,
`propTS` bigint(20) DEFAULT 0,
`p_fulfilmentstatus` bigint(20) DEFAULT NULL,
`p_notes` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_computerid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_ordercreator` bigint(20) DEFAULT NULL,
`p_fiascode` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`originaldeliverycost` decimal(30,8) DEFAULT NULL,
`p_ordernotificationstatus` bigint(20) DEFAULT NULL,
`p_chequeassigned` tinyint(1) DEFAULT NULL,
`refundeddeliverycost` decimal(30,8) DEFAULT NULL,
`p_remindsmscount` int(11) DEFAULT NULL,
`p_checkoutsuccess` tinyint(1) DEFAULT NULL,
`p_modified` tinyint(1) DEFAULT NULL,
`p_dateofstatusupdate` datetime(6) DEFAULT NULL,
`p_instorecompleted` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`PK`,`createdTS`),
KEY `OrderCode_45` (`code`),
KEY `OrderUser_45` (`userpk`),
KEY `guidIndex_45` (`p_guid`),
KEY `currencyPK_idx_45` (`currencypk`),
KEY `paymentAddressPK_idx_45` (`paymentaddresspk`),
KEY `ars_report_idx_orders_create` (`createdTS`) USING BTREE,
KEY `ars_report_idx_orders_modify` (`modifiedTS`) USING BTREE,
KEY `ars_report_idx_orders_deliveryaddresspk` (`deliveryaddresspk`) USING BTREE,
KEY `ars_report_idx_orders_delivery_dates` (`p_deliverydate`) USING BTREE,
KEY `ars_report_idx_orders_req_delivery_dates` (`p_requesteddeliverydate`) USING BTREE,
KEY `ars_report_idx_orders_deliverymodepk` (`deliverymodepk`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY RANGE COLUMNS(`createdTS`)
(PARTITION `p0` VALUES LESS THAN ('2017-03-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2017-04-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2017-05-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2017-06-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2017-07-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2017-08-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2017-09-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2017-10-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2017-11-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2017-12-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2018-01-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2018-02-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2018-03-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2018-04-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2018-05-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2018-06-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2018-07-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2018-08-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2018-09-01 00:00:00.000000') ENGINE = InnoDB)
Please add
SHOW CREATE TABLE pgrels;
SHOW CREATE TABLE usergroupslp;
SHOW CREATE TABLE orderhistoryentries;
CREATE TABLE `pgrels` (
`hjmpTS` bigint(20) DEFAULT NULL,
`createdTS` datetime(6) DEFAULT NULL,
`modifiedTS` datetime(6) DEFAULT NULL,
`TypePkString` bigint(20) DEFAULT NULL,
`OwnerPkString` bigint(20) DEFAULT NULL,
`PK` bigint(20) NOT NULL DEFAULT 0,
`languagepk` bigint(20) DEFAULT NULL,
`Qualifier` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`SourcePK` bigint(20) DEFAULT NULL,
`TargetPK` bigint(20) DEFAULT NULL,
`SequenceNumber` int(11) DEFAULT 0,
`RSequenceNumber` int(11) DEFAULT 0,
`aCLTS` bigint(20) DEFAULT 0,
`propTS` bigint(20) DEFAULT 0,
PRIMARY KEY (`PK`),
KEY `seqnr_201` (`SequenceNumber`),
KEY `rseqnr_201` (`RSequenceNumber`),
KEY `linksource_201` (`SourcePK`),
KEY `linktarget_201` (`TargetPK`),
KEY `qualifier_201` (`Qualifier`)
CREATE TABLE `usergroupslp` (
`ITEMPK` bigint(20) NOT NULL DEFAULT 0,
`ITEMTYPEPK` bigint(20) DEFAULT NULL,
`LANGPK` bigint(20) NOT NULL DEFAULT 0,
`p_locname` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`ITEMPK`,`LANGPK`),
KEY `ars_report_idx_usergroupslp_itempk_langpk` (`ITEMPK`,`LANGPK`)
CREATE TABLE `orderhistoryentries` (
`hjmpTS` bigint(20) DEFAULT NULL,
`createdTS` datetime(6) DEFAULT NULL,
`modifiedTS` datetime(6) DEFAULT NULL,
`TypePkString` bigint(20) DEFAULT NULL,
`OwnerPkString` bigint(20) DEFAULT NULL,
`PK` bigint(20) NOT NULL DEFAULT 0,
`p_timestamp` datetime(6) DEFAULT NULL,
`p_employee` bigint(20) DEFAULT NULL,
`p_description` text COLLATE utf8_bin DEFAULT NULL,
`p_previousorderversion` bigint(20) DEFAULT NULL,
`p_orderpos` int(11) DEFAULT NULL,
`p_order` bigint(20) DEFAULT NULL,
`p_code` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`aCLTS` bigint(20) DEFAULT 0,
`propTS` bigint(20) DEFAULT 0,
`p_orderstatus` bigint(20) DEFAULT NULL,
`p_ordersubstatus` bigint(20) DEFAULT NULL,
`p_deliverymode` bigint(20) DEFAULT NULL,
`p_prevstatus` bigint(20) DEFAULT NULL,
PRIMARY KEY (`PK`),
KEY `orderRelIDX_2002` (`p_order`),
KEY `orderposPosIDX_2002` (`p_orderpos`),
KEY `ars_report_idx_orderhistoryentries_3` (`p_orderstatus`,`p_prevstatus`,`p_order`),
KEY `ars_report_idx_orderhistoryentries_prevstatus` (`p_prevstatus`),
KEY `ars_report_idx_orderhistoryentries_createdTS` (`createdTS`),
KEY `ars_report_idx_orderhistoryentries_modifiedTS` (`modifiedTS`)
Sorry, missed one table - SHOW CREATE TABLE ars_report_ru_lang_pk;
please add also ANALYZE format=json select * from ars_report_full_order_status_history; (in 10.1.15 vs 10.3.7)
What is the execution time, is it much more slow in 10.3.7 then?
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ars_report_ru_lang_pk` AS select `l`.`PK` AS `pk` from `languages` `l` where `l`.`isocode` = 'ru'
10.1.15
| { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 340902, "table": { "table_name": "<derived2>", "access_type": "ALL", "r_loops": 1, "rows": 110112520, "r_rows": 3.52e6, "r_total_time_ms": 9695.4, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 2, "r_loops": 1, "r_total_time_ms": 338095, "filesort": { "r_loops": 1, "r_total_time_ms": 4696, "r_used_priority_queue": false, "r_output_rows": 4314317, "r_buffer_size": "172Mb", "temporary_table": { "table": { "table_name": "o", "partitions": [ "p0", "p1", "p2", "p3", "p4", "p5", "p6", "p7", "p8", "p9", "p10", "p11", "p12", "p13", "p14", "p15", "p16", "p17", "p18" ], "access_type": "index", "possible_keys": ["PRIMARY"], "key": "ars_report_idx_orders_create", "key_length": "8", "used_key_parts": ["createdTS"], "r_loops": 1, "rows": 5505626, "r_rows": 5.85e6, "r_total_time_ms": 6045.2, "filtered": 100, "r_filtered": 100, "attached_condition": "(o.PK is not null)", "using_index": true } , , , , , Unknown macro: { "select_id"}
} |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 11949 warnings (5 min 40.77 sec)
10.3.7
ANALYZE{
"query_block":{
"select_id":1,
"r_loops":1,
"r_total_time_ms":9.41e6,
"table":{
"table_name":"<derived2>",
"access_type":"ALL",
"r_loops":1,
"rows":922020870720,
"r_rows":3.52e6,
"r_total_time_ms":5874.3,
"filtered":100,
"r_filtered":100,
"materialized":{
"query_block":{
"select_id":2,
"r_loops":1,
"r_total_time_ms":9.41e6,
"filesort":{
"sort_key":"oh.p_order, oh.p_prevstatus, oh.p_orderstatus",
"r_loops":1,
"r_total_time_ms":3667.1,
"r_used_priority_queue":false,
"r_output_rows":4313872,
"r_buffer_size":"172Mb",
"temporary_table":{
"table":{
"table_name":"<derived4>",
"access_type":"ALL",
"r_loops":1,
"rows":51648,
"r_rows":2420,
"r_total_time_ms":2.064,
"filtered":100,
"r_filtered":100,
"materialized":{
"query_block":{
"select_id":4,
"r_loops":1,
"r_total_time_ms":22.14,
"filesort":{
"sort_key":"u.PK",
"r_loops":1,
"r_total_time_ms":0.4841,
"r_used_priority_queue":false,
"r_output_rows":2487,
"r_buffer_size":"58Kb",
"temporary_table":{
"table":
,
"table":
,
"table":
,
"subqueries":[
{
"query_block":{
"select_id":5,
"r_loops":1,
"r_total_time_ms":0.0439,
"table":
}
}
]
}
}
}
}
},
"block-nl-join":{
"table":
,
"buffer_type":"flat",
"buffer_size":"256Kb",
"join_type":"BNL",
"attached_condition":"oh.p_employee = u.user_id and (oh.p_orderstatus is not null or oh.p_prevstatus is not null)",
"r_filtered":0.0057
},
"table":
}
}
}
}
}
}
}
1 row in set (2 hours 36 min 56.587 sec)
I can not reproduce it for now. Tried with 16487.sql
Could you please add output of "show variables like 'optimizer_switch'\G", to be sure, that I used the same.
Will you be able to provide dump of your data (on ftp://ftp.askmonty.org/private/)?
|
|
MariaDB [test]> analyze select * from v4;
|
+------+-------------+------------+--------+-------------------------------------------------------------------+------------+---------+-------------------------+-------+----------+----------+------------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+------------+--------+-------------------------------------------------------------------+------------+---------+-------------------------+-------+----------+----------+------------+-----------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 49910 | 5.00 | 100.00 | 100.00 | |
|
| 2 | DERIVED | oh | ALL | orderRelIDX_2002,ars_report_idx_t3_3,ars_report_idx_t3_prevstatus | NULL | NULL | NULL | 9983 | 10000.00 | 49.99 | 100.00 | Using where; Using filesort |
|
| 2 | DERIVED | o | ref | PRIMARY | PRIMARY | 8 | test.oh.p_order | 1 | 1.00 | 100.00 | 100.00 | Using index |
|
| 2 | DERIVED | <derived4> | ref | key0 | key0 | 8 | test.oh.p_employee | 10 | 1.00 | 100.00 | 100.00 | |
|
| 4 | DERIVED | pgr | ALL | linksource_201,linktarget_201 | NULL | NULL | NULL | 10000 | 10000.00 | 100.00 | 100.00 | Using where; Using filesort |
|
| 4 | DERIVED | u | eq_ref | PRIMARY | PRIMARY | 8 | test.pgr.SourcePK | 1 | 1.00 | 100.00 | 100.00 | |
|
| 4 | DERIVED | ugl | eq_ref | PRIMARY,ars_report_idx_t2_itempk_langpk | PRIMARY | 16 | test.pgr.TargetPK,const | 1 | 1.00 | 100.00 | 100.00 | Using where |
|
| 5 | SUBQUERY | l | ref | ISOCode_32 | ISOCode_32 | 768 | const | 1 | 1.00 | 100.00 | 100.00 | Using where; Using index |
|
+------+-------------+------------+--------+-------------------------------------------------------------------+------------+---------+-------------------------+-------+----------+----------+------------+-----------------------------+
|
8 rows in set (0.197 sec)
|
|
|
MariaDB [test]> set optimizer_switch='derived_with_keys=off';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> analyze select * from v4;
|
|
|
+------+-------------+------------+--------+-------------------------------------------------------------------+------------+---------+-------------------------+----------+----------+----------+------------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+------------+--------+-------------------------------------------------------------------+------------+---------+-------------------------+----------+----------+----------+------------+-------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 49910000 | 5.00 | 100.00 | 100.00 | |
|
| 2 | DERIVED | oh | ALL | orderRelIDX_2002,ars_report_idx_t3_3,ars_report_idx_t3_prevstatus | NULL | NULL | NULL | 9983 | 10000.00 | 49.99 | 100.00 | Using where; Using temporary; Using filesort |
|
| 2 | DERIVED | o | ref | PRIMARY | PRIMARY | 8 | test.oh.p_order | 1 | 1.00 | 100.00 | 100.00 | Using index |
|
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 10000 | 10000.00 | 100.00 | 0.01 | Using where; Using join buffer (flat, BNL join) |
|
| 4 | DERIVED | pgr | ALL | linksource_201,linktarget_201 | NULL | NULL | NULL | 10000 | 10000.00 | 100.00 | 100.00 | Using where; Using filesort |
|
| 4 | DERIVED | u | eq_ref | PRIMARY | PRIMARY | 8 | test.pgr.SourcePK | 1 | 1.00 | 100.00 | 100.00 | |
|
| 4 | DERIVED | ugl | eq_ref | PRIMARY,ars_report_idx_t2_itempk_langpk | PRIMARY | 16 | test.pgr.TargetPK,const | 1 | 1.00 | 100.00 | 100.00 | Using where |
|
| 5 | SUBQUERY | l | ref | ISOCode_32 | ISOCode_32 | 768 | const | 1 | 1.00 | 100.00 | 100.00 | Using where; Using index |
|
+------+-------------+------------+--------+-------------------------------------------------------------------+------------+---------+-------------------------+----------+----------+----------+------------+-------------------------------------------------+
|
8 rows in set (9.092 sec)
|
|
|
MariaDB [test]> show variables like 'optimizer_switch'\G
|
*************************** 1. row ***************************
|
Variable_name: optimizer_switch
|
Value: 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,orderby_uses_equalities=on,condition_pushdown_for_derived=off,split_materialized=on
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> select version();
|
+----------------+
|
| version() |
|
+----------------+
|
| 10.3.7-MariaDB |
|
+----------------+
|
1 row in set (0.000 sec)
|
|
@Zakharov Sergey I found this ticket, while dealing with same problem. First it looks like a MariaDB problem, as after migrating the database to an older server the problem vanished. Together with your ticket report, we were quit sure that this is also our problem.
Finally the cause was a different one: One table was somehow broken, only an ex-/import solved the problem, we tried all known repair options - finally we used a select Into to build the table new and the problem vanished. The only difference we identified was the idnex length of the affected key, but found no means to repair/restore it in the affected table.
Could you please provide SHOW CREATE VIEW and structures of involved tables.
I assume that my.cnf is the same as in
MDEV-16420, please correct me if it is not.I checked on some easy examples, optimization works fine, so I need some minimal testcase to reproduce the bug.