Re: Got timeout writing communication packets
Posted by: M.Manigandan Mangundu
Date: August 06, 2021 08:02AM
Date: August 06, 2021 08:02AM
mysql> show create table productanalytics\G
*************************** 1. row ***************************
Table: productanalytics
Create Table: CREATE TABLE `productanalytics` (
`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,
`P_PRODUCTCODE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`P_CURRENCY` bigint(20) DEFAULT NULL,
`p_totalorders` double DEFAULT NULL,
`p_totalproductviews` double DEFAULT NULL,
`p_totalcartadds` double DEFAULT NULL,
`p_totalvisits` double DEFAULT NULL,
`p_totalunits` double DEFAULT NULL,
`p_datetime` datetime(6) DEFAULT NULL,
`p_customscore1` double DEFAULT NULL,
`p_customscore2` double DEFAULT NULL,
`p_customscore3` double DEFAULT NULL,
`ACLTS` bigint(20) DEFAULT NULL,
`PROPTS` bigint(20) DEFAULT NULL,
`sealed` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`PK`),
KEY `ANALYTICSCOMPOSITEIDX_12403` (`P_PRODUCTCODE`,`P_CURRENCY`),
KEY `ANALYTICSCURRENCYIDX_12403` (`P_CURRENCY`),
KEY `ANALYTICSPRODUCTIDX_12403` (`P_PRODUCTCODE`),
KEY `analyticsDateTimeIdx_12403` (`p_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> explain SELECT ytd.productcode,ytd.currency,ytd.totalorders,ytd.totalvisits,ytd.totalproductviews,ytd.totalcartadds,ytd.totalunits,ytd.customscore1,ytd.customscore2,ytd.customscore3,14day.totalorders,14day.totalvisits,14day.totalproductviews,14day.totalcartadds,14day.totalunits,14day.customscore1,14day.customscore2,14day.customscore3,7day.totalorders,7day.totalvisits,7day.totalproductviews,7day.totalcartadds,7day.totalunits,7day.customscore1,7day.customscore2,7day.customscore3,1day.totalorders,1day.totalvisits,1day.totalproductviews,1day.totalcartadds,1day.totalunits,1day.customscore1,1day.customscore2,1day.customscore3
-> FROM
-> (
-> SELECT item_t0.p_productcode as productcode, item_t0.p_currency as currency, SUM( item_t0.p_totalorders ) totalorders, SUM( item_t0.p_totalvisits ) totalvisits, SUM( item_t0.p_totalproductviews ) totalproductviews, SUM( item_t0.p_totalcartadds ) totalcartadds, SUM( item_t0.p_totalunits ) totalunits, SUM( item_t0.p_customscore1 ) customscore1, SUM( item_t0.p_customscore2 ) customscore2, SUM( item_t0.p_customscore3 ) customscore3
-> FROM productanalytics item_t0 WHERE ( item_t0.p_datetime >= '2021-01-01 00:00:00' AND item_t0.p_datetime <= '2021-07-28 23:59:59') AND (item_t0.TypePkString=8796126871634 ) GROUP BY item_t0.p_productcode , item_t0.p_currency
-> ) ytd
-> LEFT JOIN (
-> SELECT item_t1.p_productcode as productcode, item_t1.p_currency as currency, SUM( item_t1.p_totalorders ) totalorders, SUM( item_t1.p_totalvisits ) totalvisits, SUM( item_t1.p_totalproductviews ) totalproductviews, SUM( item_t1.p_totalcartadds ) totalcartadds, SUM( item_t1.p_totalunits ) totalunits, SUM( item_t1.p_customscore1 ) customscore1, SUM( item_t1.p_customscore2 ) customscore2, SUM( item_t1.p_customscore3 ) customscore3
-> FROM productanalytics item_t1
-> WHERE ( item_t1.p_datetime >= '2021-07-15 00:00:00' AND item_t1.p_datetime <= '2021-07-28 23:59:59') AND (item_t1.TypePkString=8796126871634 ) GROUP BY item_t1.p_productcode , item_t1.p_currency ) 14day ON ytd.productcode=14day.productcode AND ytd.currency=14day.currency
-> LEFT JOIN (
-> SELECT item_t2.p_productcode as productcode, item_t2.p_currency as currency, SUM( item_t2.p_totalorders ) totalorders, SUM( item_t2.p_totalvisits ) totalvisits, SUM( item_t2.p_totalproductviews ) totalproductviews, SUM( item_t2.p_totalcartadds ) totalcartadds, SUM( item_t2.p_totalunits ) totalunits, SUM( item_t2.p_customscore1 ) customscore1, SUM( item_t2.p_customscore2 ) customscore2, SUM( item_t2.p_customscore3 ) customscore3
-> FROM productanalytics item_t2
-> WHERE ( item_t2.p_datetime >= '2021-07-22 00:00:00' AND item_t2.p_datetime <= '2021-07-28 23:59:59') AND (item_t2.TypePkString=8796126871634 ) GROUP BY item_t2.p_productcode , item_t2.p_currency
-> ) 7day ON ytd.productcode=7day.productcode AND ytd.currency=7day.currency
-> LEFT JOIN (
-> SELECT item_t3.p_productcode as productcode, item_t3.p_currency as currency, SUM( item_t3.p_totalorders ) totalorders, SUM( item_t3.p_totalvisits ) totalvisits, SUM( item_t3.p_totalproductviews ) totalproductviews, SUM( item_t3.p_totalcartadds ) totalcartadds, SUM( item_t3.p_totalunits ) totalunits, SUM( item_t3.p_customscore1 ) customscore1, SUM( item_t3.p_customscore2 ) customscore2, SUM( item_t3.p_customscore3 ) customscore3
-> FROM productanalytics item_t3
-> WHERE ( item_t3.p_datetime >= '2021-07-28 00:00:00' AND item_t3.p_datetime <= '2021-07-28 23:59:59') AND (item_t3.TypePkString=8796126871634 ) GROUP BY item_t3.p_productcode , item_t3.p_currency
-> ) 1day ON ytd.productcode=1day.productcode AND ytd.currency=1day.currency\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 62855913
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: <derived3>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 777
ref: ytd.productcode,ytd.currency
rows: 10
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: <derived4>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 777
ref: ytd.productcode,ytd.currency
rows: 10
filtered: 100.00
Extra: NULL
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: <derived5>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 777
ref: ytd.productcode,ytd.currency
rows: 10
filtered: 100.00
Extra: NULL
*************************** 5. row ***************************
id: 5
select_type: DERIVED
table: item_t3
partitions: NULL
type: range
possible_keys: ANALYTICSCOMPOSITEIDX_12403,analyticsDateTimeIdx_12403
key: analyticsDateTimeIdx_12403
key_len: 9
ref: NULL
rows: 567720
filtered: 100.00
Extra: Using index condition; Using where; Using temporary; Using filesort
*************************** 6. row ***************************
id: 4
select_type: DERIVED
table: item_t2
partitions: NULL
type: range
possible_keys: ANALYTICSCOMPOSITEIDX_12403,analyticsDateTimeIdx_12403
key: analyticsDateTimeIdx_12403
key_len: 9
ref: NULL
rows: 3969784
filtered: 100.00
Extra: Using index condition; Using where; Using temporary; Using filesort
*************************** 7. row ***************************
id: 3
select_type: DERIVED
table: item_t1
partitions: NULL
type: range
possible_keys: ANALYTICSCOMPOSITEIDX_12403,analyticsDateTimeIdx_12403
key: analyticsDateTimeIdx_12403
key_len: 9
ref: NULL
rows: 7060600
filtered: 100.00
Extra: Using index condition; Using where; Using temporary; Using filesort
*************************** 8. row ***************************
id: 2
select_type: DERIVED
table: item_t0
partitions: NULL
type: index
possible_keys: ANALYTICSCOMPOSITEIDX_12403,analyticsDateTimeIdx_12403
key: ANALYTICSCOMPOSITEIDX_12403
key_len: 777
ref: NULL
rows: 125711826
filtered: 100.00
Extra: Using where
8 rows in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `ytd`.`productcode` AS `productcode`,`ytd`.`currency` AS `currency`,`ytd`.`totalorders` AS `totalorders`,`ytd`.`totalvisits` AS `totalvisits`,`ytd`.`totalproductviews` AS `totalproductviews`,`ytd`.`totalcartadds` AS `totalcartadds`,`ytd`.`totalunits` AS `totalunits`,`ytd`.`customscore1` AS `customscore1`,`ytd`.`customscore2` AS `customscore2`,`ytd`.`customscore3` AS `customscore3`,`14day`.`totalorders` AS `totalorders`,`14day`.`totalvisits` AS `totalvisits`,`14day`.`totalproductviews` AS `totalproductviews`,`14day`.`totalcartadds` AS `totalcartadds`,`14day`.`totalunits` AS `totalunits`,`14day`.`customscore1` AS `customscore1`,`14day`.`customscore2` AS `customscore2`,`14day`.`customscore3` AS `customscore3`,`7day`.`totalorders` AS `totalorders`,`7day`.`totalvisits` AS `totalvisits`,`7day`.`totalproductviews` AS `totalproductviews`,`7day`.`totalcartadds` AS `totalcartadds`,`7day`.`totalunits` AS `totalunits`,`7day`.`customscore1` AS `customscore1`,`7day`.`customscore2` AS `customscore2`,`7day`.`customscore3` AS `customscore3`,`1day`.`totalorders` AS `totalorders`,`1day`.`totalvisits` AS `totalvisits`,`1day`.`totalproductviews` AS `totalproductviews`,`1day`.`totalcartadds` AS `totalcartadds`,`1day`.`totalunits` AS `totalunits`,`1day`.`customscore1` AS `customscore1`,`1day`.`customscore2` AS `customscore2`,`1day`.`customscore3` AS `customscore3` from (/* select#2 */ select `hybrisdbuser`.`item_t0`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t0`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t0`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t0`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t0`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t0`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t0`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t0`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t0`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t0`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t0` where ((`hybrisdbuser`.`item_t0`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t0`.`p_datetime` >= '2021-01-01 00:00:00') and (`hybrisdbuser`.`item_t0`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t0`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t0`.`P_CURRENCY`) `ytd` left join (/* select#3 */ select `hybrisdbuser`.`item_t1`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t1`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t1`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t1`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t1`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t1`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t1`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t1`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t1`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t1`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t1` where ((`hybrisdbuser`.`item_t1`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t1`.`p_datetime` >= '2021-07-15 00:00:00') and (`hybrisdbuser`.`item_t1`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t1`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t1`.`P_CURRENCY`) `14day` on(((`14day`.`currency` = `ytd`.`currency`) and (`14day`.`productcode` = `ytd`.`productcode`))) left join (/* select#4 */ select `hybrisdbuser`.`item_t2`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t2`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t2`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t2`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t2`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t2`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t2`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t2`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t2`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t2`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t2` where ((`hybrisdbuser`.`item_t2`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t2`.`p_datetime` >= '2021-07-22 00:00:00') and (`hybrisdbuser`.`item_t2`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t2`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t2`.`P_CURRENCY`) `7day` on(((`7day`.`currency` = `ytd`.`currency`) and (`7day`.`productcode` = `ytd`.`productcode`))) left join (/* select#5 */ select `hybrisdbuser`.`item_t3`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t3`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t3`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t3`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t3`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t3`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t3`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t3`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t3`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t3`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t3` where ((`hybrisdbuser`.`item_t3`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t3`.`p_datetime` >= '2021-07-28 00:00:00') and (`hybrisdbuser`.`item_t3`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t3`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t3`.`P_CURRENCY`) `1day` on(((`1day`.`currency` = `ytd`.`currency`) and (`1day`.`productcode` = `ytd`.`productcode`))) where 1
1 row in set (0.00 sec)
mysql> explain format=json SELECT ytd.productcode,ytd.currency,ytd.totalorders,ytd.totalvisits,ytd.totalproductviews,ytd.totalcartadds,ytd.totalunits,ytd.customscore1,ytd.customscore2,ytd.customscore3,14day.totalorders,14day.totalvisits,14day.totalproductviews,14day.totalcartadds,14day.totalunits,14day.customscore1,14day.customscore2,14day.customscore3,7day.totalorders,7day.totalvisits,7day.totalproductviews,7day.totalcartadds,7day.totalunits,7day.customscore1,7day.customscore2,7day.customscore3,1day.totalorders,1day.totalvisits,1day.totalproductviews,1day.totalcartadds,1day.totalunits,1day.customscore1,1day.customscore2,1day.customscore3
-> FROM
-> (
-> SELECT item_t0.p_productcode as productcode, item_t0.p_currency as currency, SUM( item_t0.p_totalorders ) totalorders, SUM( item_t0.p_totalvisits ) totalvisits, SUM( item_t0.p_totalproductviews ) totalproductviews, SUM( item_t0.p_totalcartadds ) totalcartadds, SUM( item_t0.p_totalunits ) totalunits, SUM( item_t0.p_customscore1 ) customscore1, SUM( item_t0.p_customscore2 ) customscore2, SUM( item_t0.p_customscore3 ) customscore3
-> FROM productanalytics item_t0 WHERE ( item_t0.p_datetime >= '2021-01-01 00:00:00' AND item_t0.p_datetime <= '2021-07-28 23:59:59') AND (item_t0.TypePkString=8796126871634 ) GROUP BY item_t0.p_productcode , item_t0.p_currency
-> ) ytd
-> LEFT JOIN (
-> SELECT item_t1.p_productcode as productcode, item_t1.p_currency as currency, SUM( item_t1.p_totalorders ) totalorders, SUM( item_t1.p_totalvisits ) totalvisits, SUM( item_t1.p_totalproductviews ) totalproductviews, SUM( item_t1.p_totalcartadds ) totalcartadds, SUM( item_t1.p_totalunits ) totalunits, SUM( item_t1.p_customscore1 ) customscore1, SUM( item_t1.p_customscore2 ) customscore2, SUM( item_t1.p_customscore3 ) customscore3
-> FROM productanalytics item_t1
-> WHERE ( item_t1.p_datetime >= '2021-07-15 00:00:00' AND item_t1.p_datetime <= '2021-07-28 23:59:59') AND (item_t1.TypePkString=8796126871634 ) GROUP BY item_t1.p_productcode , item_t1.p_currency ) 14day ON ytd.productcode=14day.productcode AND ytd.currency=14day.currency
-> LEFT JOIN (
-> SELECT item_t2.p_productcode as productcode, item_t2.p_currency as currency, SUM( item_t2.p_totalorders ) totalorders, SUM( item_t2.p_totalvisits ) totalvisits, SUM( item_t2.p_totalproductviews ) totalproductviews, SUM( item_t2.p_totalcartadds ) totalcartadds, SUM( item_t2.p_totalunits ) totalunits, SUM( item_t2.p_customscore1 ) customscore1, SUM( item_t2.p_customscore2 ) customscore2, SUM( item_t2.p_customscore3 ) customscore3
-> FROM productanalytics item_t2
-> WHERE ( item_t2.p_datetime >= '2021-07-22 00:00:00' AND item_t2.p_datetime <= '2021-07-28 23:59:59') AND (item_t2.TypePkString=8796126871634 ) GROUP BY item_t2.p_productcode , item_t2.p_currency
-> ) 7day ON ytd.productcode=7day.productcode AND ytd.currency=7day.currency
-> LEFT JOIN (
-> SELECT item_t3.p_productcode as productcode, item_t3.p_currency as currency, SUM( item_t3.p_totalorders ) totalorders, SUM( item_t3.p_totalvisits ) totalvisits, SUM( item_t3.p_totalproductviews ) totalproductviews, SUM( item_t3.p_totalcartadds ) totalcartadds, SUM( item_t3.p_totalunits ) totalunits, SUM( item_t3.p_customscore1 ) customscore1, SUM( item_t3.p_customscore2 ) customscore2, SUM( item_t3.p_customscore3 ) customscore3
-> FROM productanalytics item_t3
-> WHERE ( item_t3.p_datetime >= '2021-07-28 00:00:00' AND item_t3.p_datetime <= '2021-07-28 23:59:59') AND (item_t3.TypePkString=8796126871634 ) GROUP BY item_t3.p_productcode , item_t3.p_currency
-> ) 1day ON ytd.productcode=1day.productcode AND ytd.currency=1day.currency\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "83739873705.66"
},
"nested_loop": [
{
"table": {
"table_name": "ytd",
"access_type": "ALL",
"rows_examined_per_scan": 62855913,
"rows_produced_per_join": 62855913,
"filtered": "100.00",
"cost_info": {
"read_cost": "3142805.65",
"eval_cost": "12571182.60",
"prefix_cost": "15713988.25",
"data_read_per_join": "49G"
},
"used_columns": [
"productcode",
"currency",
"totalorders",
"totalvisits",
"totalproductviews",
"totalcartadds",
"totalunits",
"customscore1",
"customscore2",
"customscore3"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "26591582.20"
},
"grouping_operation": {
"using_filesort": false,
"table": {
"table_name": "item_t0",
"access_type": "index",
"possible_keys": [
"ANALYTICSCOMPOSITEIDX_12403",
"analyticsDateTimeIdx_12403"
],
"key": "ANALYTICSCOMPOSITEIDX_12403",
"used_key_parts": [
"P_PRODUCTCODE",
"P_CURRENCY"
],
"key_length": "777",
"rows_examined_per_scan": 125711826,
"rows_produced_per_join": 62855913,
"filtered": "100.00",
"cost_info": {
"read_cost": "14020399.60",
"eval_cost": "12571182.60",
"prefix_cost": "26591582.20",
"data_read_per_join": "53G"
},
"used_columns": [
"TYPEPKSTRING",
"PK",
"P_PRODUCTCODE",
"P_CURRENCY",
"p_totalorders",
"p_totalproductviews",
"p_totalcartadds",
"p_totalvisits",
"p_totalunits",
"p_datetime",
"p_customscore1",
"p_customscore2",
"p_customscore3"
],
"attached_condition": "((`hybrisdbuser`.`item_t0`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t0`.`p_datetime` >= '2021-01-01 00:00:00') and (`hybrisdbuser`.`item_t0`.`p_datetime` <= '2021-07-28 23:59:59'))"
}
}
}
}
}
},
{
"table": {
"table_name": "14day",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"productcode",
"currency"
],
"key_length": "777",
"ref": [
"ytd.productcode",
"ytd.currency"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 628559130,
"filtered": "100.00",
"cost_info": {
"read_cost": "628559130.00",
"eval_cost": "125711826.00",
"prefix_cost": "769984944.25",
"data_read_per_join": "496G"
},
"used_columns": [
"productcode",
"currency",
"totalorders",
"totalvisits",
"totalproductviews",
"totalcartadds",
"totalunits",
"customscore1",
"customscore2",
"customscore3"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "16945441.01"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "7060600.00"
},
"table": {
"table_name": "item_t1",
"access_type": "range",
"possible_keys": [
"ANALYTICSCOMPOSITEIDX_12403",
"analyticsDateTimeIdx_12403"
],
"key": "analyticsDateTimeIdx_12403",
"used_key_parts": [
"p_datetime"
],
"key_length": "9",
"rows_examined_per_scan": 7060600,
"rows_produced_per_join": 7060600,
"filtered": "100.00",
"index_condition": "((`hybrisdbuser`.`item_t1`.`p_datetime` >= '2021-07-15 00:00:00') and (`hybrisdbuser`.`item_t1`.`p_datetime` <= '2021-07-28 23:59:59'))",
"cost_info": {
"read_cost": "8472721.01",
"eval_cost": "1412120.00",
"prefix_cost": "9884841.01",
"data_read_per_join": "6G"
},
"used_columns": [
"TYPEPKSTRING",
"PK",
"P_PRODUCTCODE",
"P_CURRENCY",
"p_totalorders",
"p_totalproductviews",
"p_totalcartadds",
"p_totalvisits",
"p_totalunits",
"p_datetime",
"p_customscore1",
"p_customscore2",
"p_customscore3"
],
"attached_condition": "(`hybrisdbuser`.`item_t1`.`TYPEPKSTRING` = 8796126871634)"
}
}
}
}
}
},
{
"table": {
"table_name": "7day",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"productcode",
"currency"
],
"key_length": "777",
"ref": [
"ytd.productcode",
"ytd.currency"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 6285597633,
"filtered": "100.00",
"cost_info": {
"read_cost": "6285597633.44",
"eval_cost": "1257119526.69",
"prefix_cost": "8312702104.38",
"data_read_per_join": "4T"
},
"used_columns": [
"productcode",
"currency",
"totalorders",
"totalvisits",
"totalproductviews",
"totalcartadds",
"totalunits",
"customscore1",
"customscore2",
"customscore3"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 4,
"cost_info": {
"query_cost": "9527482.61"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "3969784.00"
},
"table": {
"table_name": "item_t2",
"access_type": "range",
"possible_keys": [
"ANALYTICSCOMPOSITEIDX_12403",
"analyticsDateTimeIdx_12403"
],
"key": "analyticsDateTimeIdx_12403",
"used_key_parts": [
"p_datetime"
],
"key_length": "9",
"rows_examined_per_scan": 3969784,
"rows_produced_per_join": 3969784,
"filtered": "100.00",
"index_condition": "((`hybrisdbuser`.`item_t2`.`p_datetime` >= '2021-07-22 00:00:00') and (`hybrisdbuser`.`item_t2`.`p_datetime` <= '2021-07-28 23:59:59'))",
"cost_info": {
"read_cost": "4763741.81",
"eval_cost": "793956.80",
"prefix_cost": "5557698.61",
"data_read_per_join": "3G"
},
"used_columns": [
"TYPEPKSTRING",
"PK",
"P_PRODUCTCODE",
"P_CURRENCY",
"p_totalorders",
"p_totalproductviews",
"p_totalcartadds",
"p_totalvisits",
"p_totalunits",
"p_datetime",
"p_customscore1",
"p_customscore2",
"p_customscore3"
],
"attached_condition": "(`hybrisdbuser`.`item_t2`.`TYPEPKSTRING` = 8796126871634)"
}
}
}
}
}
},
{
"table": {
"table_name": "1day",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"productcode",
"currency"
],
"key_length": "777",
"ref": [
"ytd.productcode",
"ytd.currency"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 62855976334,
"filtered": "100.00",
"cost_info": {
"read_cost": "62855976334.40",
"eval_cost": "12571195266.88",
"prefix_cost": "83739873705.66",
"data_read_per_join": "48T"
},
"used_columns": [
"productcode",
"currency",
"totalorders",
"totalvisits",
"totalproductviews",
"totalcartadds",
"totalunits",
"customscore1",
"customscore2",
"customscore3"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 5,
"cost_info": {
"query_cost": "1362529.01"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "567720.00"
},
"table": {
"table_name": "item_t3",
"access_type": "range",
"possible_keys": [
"ANALYTICSCOMPOSITEIDX_12403",
"analyticsDateTimeIdx_12403"
],
"key": "analyticsDateTimeIdx_12403",
"used_key_parts": [
"p_datetime"
],
"key_length": "9",
"rows_examined_per_scan": 567720,
"rows_produced_per_join": 567720,
"filtered": "100.00",
"index_condition": "((`hybrisdbuser`.`item_t3`.`p_datetime` >= '2021-07-28 00:00:00') and (`hybrisdbuser`.`item_t3`.`p_datetime` <= '2021-07-28 23:59:59'))",
"cost_info": {
"read_cost": "681265.01",
"eval_cost": "113544.00",
"prefix_cost": "794809.01",
"data_read_per_join": "498M"
},
"used_columns": [
"TYPEPKSTRING",
"PK",
"P_PRODUCTCODE",
"P_CURRENCY",
"p_totalorders",
"p_totalproductviews",
"p_totalcartadds",
"p_totalvisits",
"p_totalunits",
"p_datetime",
"p_customscore1",
"p_customscore2",
"p_customscore3"
],
"attached_condition": "(`hybrisdbuser`.`item_t3`.`TYPEPKSTRING` = 8796126871634)"
}
}
}
}
}
}
]
}
}
1 row in set, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `ytd`.`productcode` AS `productcode`,`ytd`.`currency` AS `currency`,`ytd`.`totalorders` AS `totalorders`,`ytd`.`totalvisits` AS `totalvisits`,`ytd`.`totalproductviews` AS `totalproductviews`,`ytd`.`totalcartadds` AS `totalcartadds`,`ytd`.`totalunits` AS `totalunits`,`ytd`.`customscore1` AS `customscore1`,`ytd`.`customscore2` AS `customscore2`,`ytd`.`customscore3` AS `customscore3`,`14day`.`totalorders` AS `totalorders`,`14day`.`totalvisits` AS `totalvisits`,`14day`.`totalproductviews` AS `totalproductviews`,`14day`.`totalcartadds` AS `totalcartadds`,`14day`.`totalunits` AS `totalunits`,`14day`.`customscore1` AS `customscore1`,`14day`.`customscore2` AS `customscore2`,`14day`.`customscore3` AS `customscore3`,`7day`.`totalorders` AS `totalorders`,`7day`.`totalvisits` AS `totalvisits`,`7day`.`totalproductviews` AS `totalproductviews`,`7day`.`totalcartadds` AS `totalcartadds`,`7day`.`totalunits` AS `totalunits`,`7day`.`customscore1` AS `customscore1`,`7day`.`customscore2` AS `customscore2`,`7day`.`customscore3` AS `customscore3`,`1day`.`totalorders` AS `totalorders`,`1day`.`totalvisits` AS `totalvisits`,`1day`.`totalproductviews` AS `totalproductviews`,`1day`.`totalcartadds` AS `totalcartadds`,`1day`.`totalunits` AS `totalunits`,`1day`.`customscore1` AS `customscore1`,`1day`.`customscore2` AS `customscore2`,`1day`.`customscore3` AS `customscore3` from (/* select#2 */ select `hybrisdbuser`.`item_t0`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t0`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t0`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t0`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t0`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t0`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t0`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t0`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t0`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t0`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t0` where ((`hybrisdbuser`.`item_t0`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t0`.`p_datetime` >= '2021-01-01 00:00:00') and (`hybrisdbuser`.`item_t0`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t0`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t0`.`P_CURRENCY`) `ytd` left join (/* select#3 */ select `hybrisdbuser`.`item_t1`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t1`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t1`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t1`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t1`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t1`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t1`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t1`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t1`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t1`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t1` where ((`hybrisdbuser`.`item_t1`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t1`.`p_datetime` >= '2021-07-15 00:00:00') and (`hybrisdbuser`.`item_t1`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t1`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t1`.`P_CURRENCY`) `14day` on(((`14day`.`currency` = `ytd`.`currency`) and (`14day`.`productcode` = `ytd`.`productcode`))) left join (/* select#4 */ select `hybrisdbuser`.`item_t2`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t2`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t2`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t2`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t2`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t2`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t2`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t2`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t2`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t2`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t2` where ((`hybrisdbuser`.`item_t2`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t2`.`p_datetime` >= '2021-07-22 00:00:00') and (`hybrisdbuser`.`item_t2`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t2`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t2`.`P_CURRENCY`) `7day` on(((`7day`.`currency` = `ytd`.`currency`) and (`7day`.`productcode` = `ytd`.`productcode`))) left join (/* select#5 */ select `hybrisdbuser`.`item_t3`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t3`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t3`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t3`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t3`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t3`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t3`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t3`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t3`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t3`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t3` where ((`hybrisdbuser`.`item_t3`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t3`.`p_datetime` >= '2021-07-28 00:00:00') and (`hybrisdbuser`.`item_t3`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t3`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t3`.`P_CURRENCY`) `1day` on(((`1day`.`currency` = `ytd`.`currency`) and (`1day`.`productcode` = `ytd`.`productcode`))) where 1
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: productanalytics
Create Table: CREATE TABLE `productanalytics` (
`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,
`P_PRODUCTCODE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`P_CURRENCY` bigint(20) DEFAULT NULL,
`p_totalorders` double DEFAULT NULL,
`p_totalproductviews` double DEFAULT NULL,
`p_totalcartadds` double DEFAULT NULL,
`p_totalvisits` double DEFAULT NULL,
`p_totalunits` double DEFAULT NULL,
`p_datetime` datetime(6) DEFAULT NULL,
`p_customscore1` double DEFAULT NULL,
`p_customscore2` double DEFAULT NULL,
`p_customscore3` double DEFAULT NULL,
`ACLTS` bigint(20) DEFAULT NULL,
`PROPTS` bigint(20) DEFAULT NULL,
`sealed` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`PK`),
KEY `ANALYTICSCOMPOSITEIDX_12403` (`P_PRODUCTCODE`,`P_CURRENCY`),
KEY `ANALYTICSCURRENCYIDX_12403` (`P_CURRENCY`),
KEY `ANALYTICSPRODUCTIDX_12403` (`P_PRODUCTCODE`),
KEY `analyticsDateTimeIdx_12403` (`p_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> explain SELECT ytd.productcode,ytd.currency,ytd.totalorders,ytd.totalvisits,ytd.totalproductviews,ytd.totalcartadds,ytd.totalunits,ytd.customscore1,ytd.customscore2,ytd.customscore3,14day.totalorders,14day.totalvisits,14day.totalproductviews,14day.totalcartadds,14day.totalunits,14day.customscore1,14day.customscore2,14day.customscore3,7day.totalorders,7day.totalvisits,7day.totalproductviews,7day.totalcartadds,7day.totalunits,7day.customscore1,7day.customscore2,7day.customscore3,1day.totalorders,1day.totalvisits,1day.totalproductviews,1day.totalcartadds,1day.totalunits,1day.customscore1,1day.customscore2,1day.customscore3
-> FROM
-> (
-> SELECT item_t0.p_productcode as productcode, item_t0.p_currency as currency, SUM( item_t0.p_totalorders ) totalorders, SUM( item_t0.p_totalvisits ) totalvisits, SUM( item_t0.p_totalproductviews ) totalproductviews, SUM( item_t0.p_totalcartadds ) totalcartadds, SUM( item_t0.p_totalunits ) totalunits, SUM( item_t0.p_customscore1 ) customscore1, SUM( item_t0.p_customscore2 ) customscore2, SUM( item_t0.p_customscore3 ) customscore3
-> FROM productanalytics item_t0 WHERE ( item_t0.p_datetime >= '2021-01-01 00:00:00' AND item_t0.p_datetime <= '2021-07-28 23:59:59') AND (item_t0.TypePkString=8796126871634 ) GROUP BY item_t0.p_productcode , item_t0.p_currency
-> ) ytd
-> LEFT JOIN (
-> SELECT item_t1.p_productcode as productcode, item_t1.p_currency as currency, SUM( item_t1.p_totalorders ) totalorders, SUM( item_t1.p_totalvisits ) totalvisits, SUM( item_t1.p_totalproductviews ) totalproductviews, SUM( item_t1.p_totalcartadds ) totalcartadds, SUM( item_t1.p_totalunits ) totalunits, SUM( item_t1.p_customscore1 ) customscore1, SUM( item_t1.p_customscore2 ) customscore2, SUM( item_t1.p_customscore3 ) customscore3
-> FROM productanalytics item_t1
-> WHERE ( item_t1.p_datetime >= '2021-07-15 00:00:00' AND item_t1.p_datetime <= '2021-07-28 23:59:59') AND (item_t1.TypePkString=8796126871634 ) GROUP BY item_t1.p_productcode , item_t1.p_currency ) 14day ON ytd.productcode=14day.productcode AND ytd.currency=14day.currency
-> LEFT JOIN (
-> SELECT item_t2.p_productcode as productcode, item_t2.p_currency as currency, SUM( item_t2.p_totalorders ) totalorders, SUM( item_t2.p_totalvisits ) totalvisits, SUM( item_t2.p_totalproductviews ) totalproductviews, SUM( item_t2.p_totalcartadds ) totalcartadds, SUM( item_t2.p_totalunits ) totalunits, SUM( item_t2.p_customscore1 ) customscore1, SUM( item_t2.p_customscore2 ) customscore2, SUM( item_t2.p_customscore3 ) customscore3
-> FROM productanalytics item_t2
-> WHERE ( item_t2.p_datetime >= '2021-07-22 00:00:00' AND item_t2.p_datetime <= '2021-07-28 23:59:59') AND (item_t2.TypePkString=8796126871634 ) GROUP BY item_t2.p_productcode , item_t2.p_currency
-> ) 7day ON ytd.productcode=7day.productcode AND ytd.currency=7day.currency
-> LEFT JOIN (
-> SELECT item_t3.p_productcode as productcode, item_t3.p_currency as currency, SUM( item_t3.p_totalorders ) totalorders, SUM( item_t3.p_totalvisits ) totalvisits, SUM( item_t3.p_totalproductviews ) totalproductviews, SUM( item_t3.p_totalcartadds ) totalcartadds, SUM( item_t3.p_totalunits ) totalunits, SUM( item_t3.p_customscore1 ) customscore1, SUM( item_t3.p_customscore2 ) customscore2, SUM( item_t3.p_customscore3 ) customscore3
-> FROM productanalytics item_t3
-> WHERE ( item_t3.p_datetime >= '2021-07-28 00:00:00' AND item_t3.p_datetime <= '2021-07-28 23:59:59') AND (item_t3.TypePkString=8796126871634 ) GROUP BY item_t3.p_productcode , item_t3.p_currency
-> ) 1day ON ytd.productcode=1day.productcode AND ytd.currency=1day.currency\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 62855913
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: <derived3>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 777
ref: ytd.productcode,ytd.currency
rows: 10
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: <derived4>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 777
ref: ytd.productcode,ytd.currency
rows: 10
filtered: 100.00
Extra: NULL
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: <derived5>
partitions: NULL
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 777
ref: ytd.productcode,ytd.currency
rows: 10
filtered: 100.00
Extra: NULL
*************************** 5. row ***************************
id: 5
select_type: DERIVED
table: item_t3
partitions: NULL
type: range
possible_keys: ANALYTICSCOMPOSITEIDX_12403,analyticsDateTimeIdx_12403
key: analyticsDateTimeIdx_12403
key_len: 9
ref: NULL
rows: 567720
filtered: 100.00
Extra: Using index condition; Using where; Using temporary; Using filesort
*************************** 6. row ***************************
id: 4
select_type: DERIVED
table: item_t2
partitions: NULL
type: range
possible_keys: ANALYTICSCOMPOSITEIDX_12403,analyticsDateTimeIdx_12403
key: analyticsDateTimeIdx_12403
key_len: 9
ref: NULL
rows: 3969784
filtered: 100.00
Extra: Using index condition; Using where; Using temporary; Using filesort
*************************** 7. row ***************************
id: 3
select_type: DERIVED
table: item_t1
partitions: NULL
type: range
possible_keys: ANALYTICSCOMPOSITEIDX_12403,analyticsDateTimeIdx_12403
key: analyticsDateTimeIdx_12403
key_len: 9
ref: NULL
rows: 7060600
filtered: 100.00
Extra: Using index condition; Using where; Using temporary; Using filesort
*************************** 8. row ***************************
id: 2
select_type: DERIVED
table: item_t0
partitions: NULL
type: index
possible_keys: ANALYTICSCOMPOSITEIDX_12403,analyticsDateTimeIdx_12403
key: ANALYTICSCOMPOSITEIDX_12403
key_len: 777
ref: NULL
rows: 125711826
filtered: 100.00
Extra: Using where
8 rows in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `ytd`.`productcode` AS `productcode`,`ytd`.`currency` AS `currency`,`ytd`.`totalorders` AS `totalorders`,`ytd`.`totalvisits` AS `totalvisits`,`ytd`.`totalproductviews` AS `totalproductviews`,`ytd`.`totalcartadds` AS `totalcartadds`,`ytd`.`totalunits` AS `totalunits`,`ytd`.`customscore1` AS `customscore1`,`ytd`.`customscore2` AS `customscore2`,`ytd`.`customscore3` AS `customscore3`,`14day`.`totalorders` AS `totalorders`,`14day`.`totalvisits` AS `totalvisits`,`14day`.`totalproductviews` AS `totalproductviews`,`14day`.`totalcartadds` AS `totalcartadds`,`14day`.`totalunits` AS `totalunits`,`14day`.`customscore1` AS `customscore1`,`14day`.`customscore2` AS `customscore2`,`14day`.`customscore3` AS `customscore3`,`7day`.`totalorders` AS `totalorders`,`7day`.`totalvisits` AS `totalvisits`,`7day`.`totalproductviews` AS `totalproductviews`,`7day`.`totalcartadds` AS `totalcartadds`,`7day`.`totalunits` AS `totalunits`,`7day`.`customscore1` AS `customscore1`,`7day`.`customscore2` AS `customscore2`,`7day`.`customscore3` AS `customscore3`,`1day`.`totalorders` AS `totalorders`,`1day`.`totalvisits` AS `totalvisits`,`1day`.`totalproductviews` AS `totalproductviews`,`1day`.`totalcartadds` AS `totalcartadds`,`1day`.`totalunits` AS `totalunits`,`1day`.`customscore1` AS `customscore1`,`1day`.`customscore2` AS `customscore2`,`1day`.`customscore3` AS `customscore3` from (/* select#2 */ select `hybrisdbuser`.`item_t0`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t0`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t0`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t0`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t0`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t0`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t0`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t0`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t0`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t0`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t0` where ((`hybrisdbuser`.`item_t0`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t0`.`p_datetime` >= '2021-01-01 00:00:00') and (`hybrisdbuser`.`item_t0`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t0`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t0`.`P_CURRENCY`) `ytd` left join (/* select#3 */ select `hybrisdbuser`.`item_t1`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t1`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t1`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t1`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t1`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t1`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t1`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t1`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t1`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t1`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t1` where ((`hybrisdbuser`.`item_t1`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t1`.`p_datetime` >= '2021-07-15 00:00:00') and (`hybrisdbuser`.`item_t1`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t1`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t1`.`P_CURRENCY`) `14day` on(((`14day`.`currency` = `ytd`.`currency`) and (`14day`.`productcode` = `ytd`.`productcode`))) left join (/* select#4 */ select `hybrisdbuser`.`item_t2`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t2`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t2`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t2`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t2`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t2`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t2`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t2`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t2`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t2`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t2` where ((`hybrisdbuser`.`item_t2`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t2`.`p_datetime` >= '2021-07-22 00:00:00') and (`hybrisdbuser`.`item_t2`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t2`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t2`.`P_CURRENCY`) `7day` on(((`7day`.`currency` = `ytd`.`currency`) and (`7day`.`productcode` = `ytd`.`productcode`))) left join (/* select#5 */ select `hybrisdbuser`.`item_t3`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t3`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t3`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t3`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t3`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t3`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t3`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t3`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t3`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t3`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t3` where ((`hybrisdbuser`.`item_t3`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t3`.`p_datetime` >= '2021-07-28 00:00:00') and (`hybrisdbuser`.`item_t3`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t3`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t3`.`P_CURRENCY`) `1day` on(((`1day`.`currency` = `ytd`.`currency`) and (`1day`.`productcode` = `ytd`.`productcode`))) where 1
1 row in set (0.00 sec)
mysql> explain format=json SELECT ytd.productcode,ytd.currency,ytd.totalorders,ytd.totalvisits,ytd.totalproductviews,ytd.totalcartadds,ytd.totalunits,ytd.customscore1,ytd.customscore2,ytd.customscore3,14day.totalorders,14day.totalvisits,14day.totalproductviews,14day.totalcartadds,14day.totalunits,14day.customscore1,14day.customscore2,14day.customscore3,7day.totalorders,7day.totalvisits,7day.totalproductviews,7day.totalcartadds,7day.totalunits,7day.customscore1,7day.customscore2,7day.customscore3,1day.totalorders,1day.totalvisits,1day.totalproductviews,1day.totalcartadds,1day.totalunits,1day.customscore1,1day.customscore2,1day.customscore3
-> FROM
-> (
-> SELECT item_t0.p_productcode as productcode, item_t0.p_currency as currency, SUM( item_t0.p_totalorders ) totalorders, SUM( item_t0.p_totalvisits ) totalvisits, SUM( item_t0.p_totalproductviews ) totalproductviews, SUM( item_t0.p_totalcartadds ) totalcartadds, SUM( item_t0.p_totalunits ) totalunits, SUM( item_t0.p_customscore1 ) customscore1, SUM( item_t0.p_customscore2 ) customscore2, SUM( item_t0.p_customscore3 ) customscore3
-> FROM productanalytics item_t0 WHERE ( item_t0.p_datetime >= '2021-01-01 00:00:00' AND item_t0.p_datetime <= '2021-07-28 23:59:59') AND (item_t0.TypePkString=8796126871634 ) GROUP BY item_t0.p_productcode , item_t0.p_currency
-> ) ytd
-> LEFT JOIN (
-> SELECT item_t1.p_productcode as productcode, item_t1.p_currency as currency, SUM( item_t1.p_totalorders ) totalorders, SUM( item_t1.p_totalvisits ) totalvisits, SUM( item_t1.p_totalproductviews ) totalproductviews, SUM( item_t1.p_totalcartadds ) totalcartadds, SUM( item_t1.p_totalunits ) totalunits, SUM( item_t1.p_customscore1 ) customscore1, SUM( item_t1.p_customscore2 ) customscore2, SUM( item_t1.p_customscore3 ) customscore3
-> FROM productanalytics item_t1
-> WHERE ( item_t1.p_datetime >= '2021-07-15 00:00:00' AND item_t1.p_datetime <= '2021-07-28 23:59:59') AND (item_t1.TypePkString=8796126871634 ) GROUP BY item_t1.p_productcode , item_t1.p_currency ) 14day ON ytd.productcode=14day.productcode AND ytd.currency=14day.currency
-> LEFT JOIN (
-> SELECT item_t2.p_productcode as productcode, item_t2.p_currency as currency, SUM( item_t2.p_totalorders ) totalorders, SUM( item_t2.p_totalvisits ) totalvisits, SUM( item_t2.p_totalproductviews ) totalproductviews, SUM( item_t2.p_totalcartadds ) totalcartadds, SUM( item_t2.p_totalunits ) totalunits, SUM( item_t2.p_customscore1 ) customscore1, SUM( item_t2.p_customscore2 ) customscore2, SUM( item_t2.p_customscore3 ) customscore3
-> FROM productanalytics item_t2
-> WHERE ( item_t2.p_datetime >= '2021-07-22 00:00:00' AND item_t2.p_datetime <= '2021-07-28 23:59:59') AND (item_t2.TypePkString=8796126871634 ) GROUP BY item_t2.p_productcode , item_t2.p_currency
-> ) 7day ON ytd.productcode=7day.productcode AND ytd.currency=7day.currency
-> LEFT JOIN (
-> SELECT item_t3.p_productcode as productcode, item_t3.p_currency as currency, SUM( item_t3.p_totalorders ) totalorders, SUM( item_t3.p_totalvisits ) totalvisits, SUM( item_t3.p_totalproductviews ) totalproductviews, SUM( item_t3.p_totalcartadds ) totalcartadds, SUM( item_t3.p_totalunits ) totalunits, SUM( item_t3.p_customscore1 ) customscore1, SUM( item_t3.p_customscore2 ) customscore2, SUM( item_t3.p_customscore3 ) customscore3
-> FROM productanalytics item_t3
-> WHERE ( item_t3.p_datetime >= '2021-07-28 00:00:00' AND item_t3.p_datetime <= '2021-07-28 23:59:59') AND (item_t3.TypePkString=8796126871634 ) GROUP BY item_t3.p_productcode , item_t3.p_currency
-> ) 1day ON ytd.productcode=1day.productcode AND ytd.currency=1day.currency\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "83739873705.66"
},
"nested_loop": [
{
"table": {
"table_name": "ytd",
"access_type": "ALL",
"rows_examined_per_scan": 62855913,
"rows_produced_per_join": 62855913,
"filtered": "100.00",
"cost_info": {
"read_cost": "3142805.65",
"eval_cost": "12571182.60",
"prefix_cost": "15713988.25",
"data_read_per_join": "49G"
},
"used_columns": [
"productcode",
"currency",
"totalorders",
"totalvisits",
"totalproductviews",
"totalcartadds",
"totalunits",
"customscore1",
"customscore2",
"customscore3"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "26591582.20"
},
"grouping_operation": {
"using_filesort": false,
"table": {
"table_name": "item_t0",
"access_type": "index",
"possible_keys": [
"ANALYTICSCOMPOSITEIDX_12403",
"analyticsDateTimeIdx_12403"
],
"key": "ANALYTICSCOMPOSITEIDX_12403",
"used_key_parts": [
"P_PRODUCTCODE",
"P_CURRENCY"
],
"key_length": "777",
"rows_examined_per_scan": 125711826,
"rows_produced_per_join": 62855913,
"filtered": "100.00",
"cost_info": {
"read_cost": "14020399.60",
"eval_cost": "12571182.60",
"prefix_cost": "26591582.20",
"data_read_per_join": "53G"
},
"used_columns": [
"TYPEPKSTRING",
"PK",
"P_PRODUCTCODE",
"P_CURRENCY",
"p_totalorders",
"p_totalproductviews",
"p_totalcartadds",
"p_totalvisits",
"p_totalunits",
"p_datetime",
"p_customscore1",
"p_customscore2",
"p_customscore3"
],
"attached_condition": "((`hybrisdbuser`.`item_t0`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t0`.`p_datetime` >= '2021-01-01 00:00:00') and (`hybrisdbuser`.`item_t0`.`p_datetime` <= '2021-07-28 23:59:59'))"
}
}
}
}
}
},
{
"table": {
"table_name": "14day",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"productcode",
"currency"
],
"key_length": "777",
"ref": [
"ytd.productcode",
"ytd.currency"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 628559130,
"filtered": "100.00",
"cost_info": {
"read_cost": "628559130.00",
"eval_cost": "125711826.00",
"prefix_cost": "769984944.25",
"data_read_per_join": "496G"
},
"used_columns": [
"productcode",
"currency",
"totalorders",
"totalvisits",
"totalproductviews",
"totalcartadds",
"totalunits",
"customscore1",
"customscore2",
"customscore3"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "16945441.01"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "7060600.00"
},
"table": {
"table_name": "item_t1",
"access_type": "range",
"possible_keys": [
"ANALYTICSCOMPOSITEIDX_12403",
"analyticsDateTimeIdx_12403"
],
"key": "analyticsDateTimeIdx_12403",
"used_key_parts": [
"p_datetime"
],
"key_length": "9",
"rows_examined_per_scan": 7060600,
"rows_produced_per_join": 7060600,
"filtered": "100.00",
"index_condition": "((`hybrisdbuser`.`item_t1`.`p_datetime` >= '2021-07-15 00:00:00') and (`hybrisdbuser`.`item_t1`.`p_datetime` <= '2021-07-28 23:59:59'))",
"cost_info": {
"read_cost": "8472721.01",
"eval_cost": "1412120.00",
"prefix_cost": "9884841.01",
"data_read_per_join": "6G"
},
"used_columns": [
"TYPEPKSTRING",
"PK",
"P_PRODUCTCODE",
"P_CURRENCY",
"p_totalorders",
"p_totalproductviews",
"p_totalcartadds",
"p_totalvisits",
"p_totalunits",
"p_datetime",
"p_customscore1",
"p_customscore2",
"p_customscore3"
],
"attached_condition": "(`hybrisdbuser`.`item_t1`.`TYPEPKSTRING` = 8796126871634)"
}
}
}
}
}
},
{
"table": {
"table_name": "7day",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"productcode",
"currency"
],
"key_length": "777",
"ref": [
"ytd.productcode",
"ytd.currency"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 6285597633,
"filtered": "100.00",
"cost_info": {
"read_cost": "6285597633.44",
"eval_cost": "1257119526.69",
"prefix_cost": "8312702104.38",
"data_read_per_join": "4T"
},
"used_columns": [
"productcode",
"currency",
"totalorders",
"totalvisits",
"totalproductviews",
"totalcartadds",
"totalunits",
"customscore1",
"customscore2",
"customscore3"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 4,
"cost_info": {
"query_cost": "9527482.61"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "3969784.00"
},
"table": {
"table_name": "item_t2",
"access_type": "range",
"possible_keys": [
"ANALYTICSCOMPOSITEIDX_12403",
"analyticsDateTimeIdx_12403"
],
"key": "analyticsDateTimeIdx_12403",
"used_key_parts": [
"p_datetime"
],
"key_length": "9",
"rows_examined_per_scan": 3969784,
"rows_produced_per_join": 3969784,
"filtered": "100.00",
"index_condition": "((`hybrisdbuser`.`item_t2`.`p_datetime` >= '2021-07-22 00:00:00') and (`hybrisdbuser`.`item_t2`.`p_datetime` <= '2021-07-28 23:59:59'))",
"cost_info": {
"read_cost": "4763741.81",
"eval_cost": "793956.80",
"prefix_cost": "5557698.61",
"data_read_per_join": "3G"
},
"used_columns": [
"TYPEPKSTRING",
"PK",
"P_PRODUCTCODE",
"P_CURRENCY",
"p_totalorders",
"p_totalproductviews",
"p_totalcartadds",
"p_totalvisits",
"p_totalunits",
"p_datetime",
"p_customscore1",
"p_customscore2",
"p_customscore3"
],
"attached_condition": "(`hybrisdbuser`.`item_t2`.`TYPEPKSTRING` = 8796126871634)"
}
}
}
}
}
},
{
"table": {
"table_name": "1day",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"productcode",
"currency"
],
"key_length": "777",
"ref": [
"ytd.productcode",
"ytd.currency"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 62855976334,
"filtered": "100.00",
"cost_info": {
"read_cost": "62855976334.40",
"eval_cost": "12571195266.88",
"prefix_cost": "83739873705.66",
"data_read_per_join": "48T"
},
"used_columns": [
"productcode",
"currency",
"totalorders",
"totalvisits",
"totalproductviews",
"totalcartadds",
"totalunits",
"customscore1",
"customscore2",
"customscore3"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 5,
"cost_info": {
"query_cost": "1362529.01"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "567720.00"
},
"table": {
"table_name": "item_t3",
"access_type": "range",
"possible_keys": [
"ANALYTICSCOMPOSITEIDX_12403",
"analyticsDateTimeIdx_12403"
],
"key": "analyticsDateTimeIdx_12403",
"used_key_parts": [
"p_datetime"
],
"key_length": "9",
"rows_examined_per_scan": 567720,
"rows_produced_per_join": 567720,
"filtered": "100.00",
"index_condition": "((`hybrisdbuser`.`item_t3`.`p_datetime` >= '2021-07-28 00:00:00') and (`hybrisdbuser`.`item_t3`.`p_datetime` <= '2021-07-28 23:59:59'))",
"cost_info": {
"read_cost": "681265.01",
"eval_cost": "113544.00",
"prefix_cost": "794809.01",
"data_read_per_join": "498M"
},
"used_columns": [
"TYPEPKSTRING",
"PK",
"P_PRODUCTCODE",
"P_CURRENCY",
"p_totalorders",
"p_totalproductviews",
"p_totalcartadds",
"p_totalvisits",
"p_totalunits",
"p_datetime",
"p_customscore1",
"p_customscore2",
"p_customscore3"
],
"attached_condition": "(`hybrisdbuser`.`item_t3`.`TYPEPKSTRING` = 8796126871634)"
}
}
}
}
}
}
]
}
}
1 row in set, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `ytd`.`productcode` AS `productcode`,`ytd`.`currency` AS `currency`,`ytd`.`totalorders` AS `totalorders`,`ytd`.`totalvisits` AS `totalvisits`,`ytd`.`totalproductviews` AS `totalproductviews`,`ytd`.`totalcartadds` AS `totalcartadds`,`ytd`.`totalunits` AS `totalunits`,`ytd`.`customscore1` AS `customscore1`,`ytd`.`customscore2` AS `customscore2`,`ytd`.`customscore3` AS `customscore3`,`14day`.`totalorders` AS `totalorders`,`14day`.`totalvisits` AS `totalvisits`,`14day`.`totalproductviews` AS `totalproductviews`,`14day`.`totalcartadds` AS `totalcartadds`,`14day`.`totalunits` AS `totalunits`,`14day`.`customscore1` AS `customscore1`,`14day`.`customscore2` AS `customscore2`,`14day`.`customscore3` AS `customscore3`,`7day`.`totalorders` AS `totalorders`,`7day`.`totalvisits` AS `totalvisits`,`7day`.`totalproductviews` AS `totalproductviews`,`7day`.`totalcartadds` AS `totalcartadds`,`7day`.`totalunits` AS `totalunits`,`7day`.`customscore1` AS `customscore1`,`7day`.`customscore2` AS `customscore2`,`7day`.`customscore3` AS `customscore3`,`1day`.`totalorders` AS `totalorders`,`1day`.`totalvisits` AS `totalvisits`,`1day`.`totalproductviews` AS `totalproductviews`,`1day`.`totalcartadds` AS `totalcartadds`,`1day`.`totalunits` AS `totalunits`,`1day`.`customscore1` AS `customscore1`,`1day`.`customscore2` AS `customscore2`,`1day`.`customscore3` AS `customscore3` from (/* select#2 */ select `hybrisdbuser`.`item_t0`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t0`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t0`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t0`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t0`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t0`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t0`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t0`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t0`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t0`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t0` where ((`hybrisdbuser`.`item_t0`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t0`.`p_datetime` >= '2021-01-01 00:00:00') and (`hybrisdbuser`.`item_t0`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t0`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t0`.`P_CURRENCY`) `ytd` left join (/* select#3 */ select `hybrisdbuser`.`item_t1`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t1`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t1`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t1`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t1`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t1`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t1`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t1`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t1`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t1`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t1` where ((`hybrisdbuser`.`item_t1`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t1`.`p_datetime` >= '2021-07-15 00:00:00') and (`hybrisdbuser`.`item_t1`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t1`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t1`.`P_CURRENCY`) `14day` on(((`14day`.`currency` = `ytd`.`currency`) and (`14day`.`productcode` = `ytd`.`productcode`))) left join (/* select#4 */ select `hybrisdbuser`.`item_t2`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t2`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t2`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t2`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t2`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t2`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t2`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t2`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t2`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t2`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t2` where ((`hybrisdbuser`.`item_t2`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t2`.`p_datetime` >= '2021-07-22 00:00:00') and (`hybrisdbuser`.`item_t2`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t2`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t2`.`P_CURRENCY`) `7day` on(((`7day`.`currency` = `ytd`.`currency`) and (`7day`.`productcode` = `ytd`.`productcode`))) left join (/* select#5 */ select `hybrisdbuser`.`item_t3`.`P_PRODUCTCODE` AS `productcode`,`hybrisdbuser`.`item_t3`.`P_CURRENCY` AS `currency`,sum(`hybrisdbuser`.`item_t3`.`p_totalorders`) AS `totalorders`,sum(`hybrisdbuser`.`item_t3`.`p_totalvisits`) AS `totalvisits`,sum(`hybrisdbuser`.`item_t3`.`p_totalproductviews`) AS `totalproductviews`,sum(`hybrisdbuser`.`item_t3`.`p_totalcartadds`) AS `totalcartadds`,sum(`hybrisdbuser`.`item_t3`.`p_totalunits`) AS `totalunits`,sum(`hybrisdbuser`.`item_t3`.`p_customscore1`) AS `customscore1`,sum(`hybrisdbuser`.`item_t3`.`p_customscore2`) AS `customscore2`,sum(`hybrisdbuser`.`item_t3`.`p_customscore3`) AS `customscore3` from `hybrisdbuser`.`productanalytics` `item_t3` where ((`hybrisdbuser`.`item_t3`.`TYPEPKSTRING` = 8796126871634) and (`hybrisdbuser`.`item_t3`.`p_datetime` >= '2021-07-28 00:00:00') and (`hybrisdbuser`.`item_t3`.`p_datetime` <= '2021-07-28 23:59:59')) group by `hybrisdbuser`.`item_t3`.`P_PRODUCTCODE`,`hybrisdbuser`.`item_t3`.`P_CURRENCY`) `1day` on(((`1day`.`currency` = `ytd`.`currency`) and (`1day`.`productcode` = `ytd`.`productcode`))) where 1
1 row in set (0.00 sec)
Subject
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.