[MDEV-16487] derived_with_keys not working Created: 2018-06-14  Updated: 2018-10-01  Resolved: 2018-10-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Zakharov Sergey Assignee: Alice Sherepa
Resolution: Incomplete Votes: 0
Labels: None

Attachments: File 16487.sql    

 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                        |
 
+------+-------------+------------+--------+-----------------------------------------------------------------------------------------------------+------------------------------+---------+-----------------------+----------------+-------------------------------------------------+



 Comments   
Comment by Alice Sherepa [ 2018-06-22 ]

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.

Comment by Zakharov Sergey [ 2018-06-22 ]

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)

Comment by Alice Sherepa [ 2018-06-22 ]

Please add
SHOW CREATE TABLE pgrels;
SHOW CREATE TABLE usergroupslp;
SHOW CREATE TABLE orderhistoryentries;

Comment by Zakharov Sergey [ 2018-06-22 ]

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`)

Comment by Alice Sherepa [ 2018-06-22 ]

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?

Comment by Zakharov Sergey [ 2018-06-23 ]

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 }

,
"table":

{ "table_name": "oh", "access_type": "ref", "possible_keys": [ "orderRelIDX_2002", "ars_report_idx_orderhistoryentries_3", "ars_report_idx_orderhistoryentries_prevstatus" ], "key": "orderRelIDX_2002", "key_length": "9", "used_key_parts": ["p_order"], "ref": ["hybrisdb.o.PK"], "r_loops": 5852334, "rows": 2, "r_rows": 6.4233, "r_total_time_ms": 228467, "filtered": 100, "r_filtered": 14.572, "attached_condition": "(((oh.p_orderstatus is not null) or (oh.p_prevstatus is not null)) and (oh.p_employee is not null))" }

,
"table": {
"table_name": "<derived4>",
"access_type": "ref",
"possible_keys": ["key0"],
"key": "key0",
"key_length": "8",
"used_key_parts": ["user_id"],
"ref": ["hybrisdb.oh.p_employee"],
"r_loops": 5477887,
"rows": 10,
"r_rows": 0.7876,
"r_total_time_ms": 30884,
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"select_id": 4,
"r_loops": 1,
"r_total_time_ms": 51.994,
"filesort": {
"r_loops": 1,
"r_total_time_ms": 0.7109,
"r_used_priority_queue": false,
"r_output_rows": 2487,
"r_buffer_size": "58Kb",
"temporary_table": {
"table":

{ "table_name": "ugl", "access_type": "ALL", "possible_keys": [ "PRIMARY", "ars_report_idx_usergroupslp_itempk_langpk" ], "r_loops": 1, "rows": 64, "r_rows": 64, "r_total_time_ms": 0.0555, "filtered": 75, "r_filtered": 40.625, "attached_condition": "(ugl.LANGPK = (subquery#5))" }

,
"table":

{ "table_name": "pgr", "access_type": "ref", "possible_keys": ["linksource_201", "linktarget_201"], "key": "linktarget_201", "key_length": "9", "used_key_parts": ["TargetPK"], "ref": ["hybrisdb.ugl.ITEMPK"], "r_loops": 26, "rows": 107757, "r_rows": 96, "r_total_time_ms": 12.216, "filtered": 100, "r_filtered": 100, "attached_condition": "(pgr.SourcePK is not null)" }

,
"table":

{ "table_name": "u", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "8", "used_key_parts": ["PK"], "ref": ["hybrisdb.pgr.SourcePK"], "r_loops": 2496, "rows": 1, "r_rows": 0.9964, "r_total_time_ms": 17.168, "filtered": 100, "r_filtered": 100 }

,
"subqueries": [
{
"query_block":

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_name":"ugl",                                                                  "access_type":"ALL",                                                                  "possible_keys":[                                                                         "PRIMARY",                                                                       "ars_report_idx_usergroupslp_itempk_langpk"                                        ],                                                                  "r_loops":1,                                                                  "rows":64,                                                                  "r_rows":64,                                                                  "r_total_time_ms":0.0396,                                                                  "filtered":75,                                                                  "r_filtered":40.625,                                                                  "attached_condition":"ugl.LANGPK = (subquery#5)"                                                                            }

,
                                                            "table":

{                                                                    "table_name":"pgr",                                                                  "access_type":"ref",                                                                  "possible_keys":[                                             "linksource_201",                                           "linktarget_201"                                        ],                                                                  "key":"linktarget_201",                                                                  "key_length":"9",                                                                  "used_key_parts":[                                             "TargetPK"                                        ],                                                                  "ref":[                                             "hybrisdb.ugl.ITEMPK"                                        ],                                                                  "r_loops":26,                                                                  "rows":1076,                                                                  "r_rows":96,                                                                  "r_total_time_ms":4.0076,                                                                  "filtered":100,                                                                  "r_filtered":100,                                                                  "attached_condition":"pgr.SourcePK is not null"                                                                            }

,
                                                            "table":

{                                                                    "table_name":"u",                                                                  "access_type":"eq_ref",                                                                  "possible_keys":[                                             "PRIMARY"                                        ],                                                                  "key":"PRIMARY",                                                                  "key_length":"8",                                                                  "used_key_parts":[                                             "PK"                                        ],                                                                  "ref":[                                             "hybrisdb.pgr.SourcePK"                                        ],                                                                  "r_loops":2496,                                                                  "rows":1,                                                                  "r_rows":0.9964,                                                                  "r_total_time_ms":7.8065,                                                                  "filtered":100,                                                                  "r_filtered":100                                                                            }

,
                                                            "subqueries":[  
                                                                              {  
                                                                      "query_block":{  
                                                                           "select_id":5,
                                                                           "r_loops":1,
                                                                           "r_total_time_ms":0.0439,
                                                                           "table":

{                                                                                   "table_name":"l",                                                                                 "access_type":"ref",                                                                                 "possible_keys":[                                                      "ISOCode_32"                                                 ],                                                                                 "key":"ISOCode_32",                                                                                 "key_length":"768",                                                                                 "used_key_parts":[                                                      "isocode"                                                 ],                                                                                 "ref":[                                                      "const"                                                 ],                                                                                 "r_loops":1,                                                                                 "rows":1,                                                                                 "r_rows":1,                                                                                 "r_total_time_ms":0.0363,                                                                                 "filtered":100,                                                                                 "r_filtered":100,                                                                                 "attached_condition":"l.isocode = 'ru'",                                                                                 "using_index":true                                                                                              }

                                             
                                          }                                          
                                       }                                       
                                    ]                                    
                                 }                                 
                              }                              
                           }                           
                        }                        
                     },
                                   "block-nl-join":{  
                                        "table":

{                                                "table_name":"oh",                                              "access_type":"ALL",                                              "possible_keys":[                                                     "orderRelIDX_2002",                                                   "ars_report_idx_orderhistoryentries_3",                                                   "ars_report_idx_orderhistoryentries_prevstatus"                            ],                                              "r_loops":1,                                              "rows":35704031,                                              "r_rows":3.76e7,                                              "r_total_time_ms":82676,                                              "filtered":50,                                              "r_filtered":82.562,                                              "attached_condition":"(oh.p_orderstatus is not null or oh.p_prevstatus is not null) and oh.p_order is not null"                                                    }

,
                                        "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":

{                                           "table_name":"o",                                         "partitions":[                              I                  "p0",                                              "p1",                                              "p2",                                              "p3",                                              "p4",                                              "p5",                                              "p6",                                              "p7",                                              "p8",                                              "p9",                                              "p10",                                              "p11",                                              "p12",                                              "p13",                                              "p14",                                              "p15",                                              "p16",                                              "p17",                                              "p18"                         ],                                         "access_type":"ref",                                         "possible_keys":[                              "PRIMARY"                         ],                                         "key":"PRIMARY",                                         "key_length":"8",                                         "used_key_parts":[                              "PK"                         ],                                         "ref":[                              "hybrisdb.oh.p_order"                         ],                                         "r_loops":4313908,                                         "rows":1,                                         "r_rows":1,                                         "r_total_time_ms":296120,                                         "filtered":100,                                         "r_filtered":100,                                         "using_index":true                                              }

                     
                  }                  
               }               
            }            
         }         
      }      
   }   
}

1 row in set (2 hours 36 min 56.587 sec)

Comment by Alice Sherepa [ 2018-07-03 ]

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)

Comment by MArcel Zielinski [ 2018-09-11 ]

@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.

Generated at Thu Feb 08 08:29:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.