github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/r/explain_complex.result (about) 1 CREATE TABLE `dt` ( 2 `id` int(11) unsigned NOT NULL AUTO_INCREMENT , 3 `aid` varchar(32) NOT NULL, 4 `cm` int(10) unsigned NOT NULL, 5 `pt` varchar(10) NOT NULL, 6 `dic` varchar(64) DEFAULT NULL, 7 `ip` varchar(15) DEFAULT NULL, 8 `ds` date DEFAULT NULL, 9 `ds2` varchar(13) DEFAULT NULL , 10 `t` int(13) DEFAULT NULL , 11 `ext` varchar(550) DEFAULT NULL, 12 `p1` varchar(64) DEFAULT NULL , 13 `p2` varchar(64) DEFAULT NULL, 14 `p3` varchar(64) DEFAULT NULL, 15 `p4` varchar(64) DEFAULT NULL, 16 `p5` varchar(64) DEFAULT NULL, 17 `p6_md5` varchar(32) DEFAULT NULL, 18 `p7_md5` varchar(32) DEFAULT NULL, 19 `bm` tinyint(1) DEFAULT '0', 20 `bgm` tinyint(1) DEFAULT '0', 21 `insert_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 22 PRIMARY KEY (`id`), 23 UNIQUE KEY `aid` (`aid`,`dic`), 24 KEY `ip` (`ip`), 25 KEY `cmi` (`cm`) 26 ); 27 CREATE TABLE `st` ( 28 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT , 29 `pt` varchar(10) NOT NULL , 30 `aid` varchar(35) NOT NULL , 31 `cm` int(10) NOT NULL , 32 `ip` varchar(15) DEFAULT NULL , 33 `dic` varchar(64) DEFAULT NULL , 34 `dit` varchar(5) DEFAULT NULL, 35 `p1` varchar(64) DEFAULT NULL , 36 `p2` varchar(64) DEFAULT NULL, 37 `p3` varchar(64) DEFAULT NULL, 38 `p4` varchar(64) DEFAULT NULL, 39 `p5` varchar(64) DEFAULT NULL, 40 `p6_md5` varchar(32) DEFAULT NULL, 41 `p7_md5` varchar(32) DEFAULT NULL, 42 `ext` varchar(550) DEFAULT NULL, 43 `bm` tinyint(1) DEFAULT '0', 44 `ds` date NOT NULL , 45 `ds2` varchar(13) DEFAULT NULL , 46 `t` int(13) NOT NULL , 47 PRIMARY KEY (`id`), 48 KEY `t` (`t`), 49 KEY `icd` (`cm`,`ds`) 50 ); 51 CREATE TABLE `dd` ( 52 `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT , 53 `aid` varchar(35) NOT NULL , 54 `pt` varchar(10) NOT NULL , 55 `dic` varchar(64) NOT NULL, 56 `dim` varchar(32) NOT NULL , 57 `mac` varchar(32) DEFAULT NULL , 58 `ip` varchar(15) DEFAULT NULL , 59 `t` int(13) DEFAULT NULL , 60 `bm` tinyint(1) DEFAULT '0', 61 PRIMARY KEY (`id`), 62 UNIQUE KEY `aid` (`aid`,`dic`), 63 KEY `ip` (`ip`), 64 KEY `pi` (`aid`,`dim`), 65 KEY `t` (`t`) 66 ); 67 CREATE TABLE `pp` ( 68 `oid` varchar(20) NOT NULL, 69 `uid` bigint(20) unsigned NOT NULL, 70 `cid` int(11) unsigned NOT NULL, 71 `ppt` int(11) NOT NULL DEFAULT '0', 72 `FIDelt` int(11) DEFAULT '0', 73 `am` decimal(10,2) unsigned NOT NULL DEFAULT '0.00', 74 `cc` decimal(10,2) NOT NULL DEFAULT '0.00', 75 `ps` tinyint(1) NOT NULL, 76 `tid` varchar(200) DEFAULT NULL, 77 `ppf` varchar(50) NOT NULL, 78 `bs` tinyint(1) NOT NULL DEFAULT '0', 79 `bex` tinyint(1) NOT NULL DEFAULT '0', 80 `bu` int(11) NOT NULL DEFAULT '0', 81 `pc` char(10) NOT NULL DEFAULT 'CNY', 82 `ui` int(16) NOT NULL DEFAULT '1', 83 `cr` decimal(10,4) unsigned NOT NULL DEFAULT '1.0000', 84 `pi` int(11) unsigned NOT NULL, 85 `si` int(11) unsigned NOT NULL, 86 `bcc` int(11) NOT NULL DEFAULT '0', 87 `acc` int(11) NOT NULL DEFAULT '0', 88 KEY `oid` (`oid`), 89 KEY `uid` (`uid`), 90 KEY `ppt` (`ppt`), 91 KEY `FIDelt` (`FIDelt`), 92 KEY `cid` (`cid`), 93 KEY `ps` (`ps`), 94 KEY `sp` (`uid`,`pi`) 95 ); 96 CREATE TABLE `rr` ( 97 `aid` varchar(35) NOT NULL , 98 `pt` varchar(10) NOT NULL , 99 `dic` varchar(64) NOT NULL , 100 `gid` varchar(42) NOT NULL , 101 `acd` varchar(32) NOT NULL , 102 `t` int(13) DEFAULT NULL , 103 `bm` tinyint(1) DEFAULT '0', 104 PRIMARY KEY (`aid`,`dic`) 105 ); 106 explain SELECT `ds`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6_md5`, `p7_md5`, count(dic) as install_device FROM `dt` use index (cmi) WHERE (`ds` >= '2020-09-01') AND (`ds` <= '2020-11-03') AND (`cm` IN ('1062', '1086', '1423', '1424', '1425', '1426', '1427', '1428', '1429', '1430', '1431', '1432', '1433', '1434', '1435', '1436', '1437', '1438', '1439', '1440', '1441', '1442', '1443', '1444', '1445', '1446', '1447', '1448', '1449', '1450', '1451', '1452', '1488', '1489', '1490', '1491', '1492', '1493', '1494', '1495', '1496', '1497', '1550', '1551', '1552', '1553', '1554', '1555', '1556', '1557', '1558', '1559', '1597', '1598', '1599', '1600', '1601', '1602', '1603', '1604', '1605', '1606', '1607', '1608', '1609', '1610', '1611', '1612', '1613', '1614', '1615', '1616', '1623', '1624', '1625', '1626', '1627', '1628', '1629', '1630', '1631', '1632', '1709', '1719', '1720', '1843', '2813', '2814', '2815', '2816', '2817', '2818', '2819', '2820', '2821', '2822', '2823', '2824', '2825', '2826', '2827', '2828', '2829', '2830', '2831', '2832', '2833', '2834', '2835', '2836', '2837', '2838', '2839', '2840', '2841', '2842', '2843', '2844', '2845', '2846', '2847', '2848', '2849', '2850', '2851', '2852', '2853', '2854', '2855', '2856', '2857', '2858', '2859', '2860', '2861', '2862', '2863', '2864', '2865', '2866', '2867', '2868', '2869', '2870', '2871', '2872', '3139', '3140', '3141', '3142', '3143', '3144', '3145', '3146', '3147', '3148', '3149', '3150', '3151', '3152', '3153', '3154', '3155', '3156', '3157', '3158', '3386', '3387', '3388', '3389', '3390', '3391', '3392', '3393', '3394', '3395', '3664', '3665', '3666', '3667', '3668', '3670', '3671', '3672', '3673', '3674', '3676', '3677', '3678', '3679', '3680', '3681', '3682', '3683', '3684', '3685', '3686', '3687', '3688', '3689', '3690', '3691', '3692', '3693', '3694', '3695', '3696', '3697', '3698', '3699', '3700', '3701', '3702', '3703', '3704', '3705', '3706', '3707', '3708', '3709', '3710', '3711', '3712', '3713', '3714', '3715', '3960', '3961', '3962', '3963', '3964', '3965', '3966', '3967', '3968', '3978', '3979', '3980', '3981', '3982', '3983', '3984', '3985', '3986', '3987', '4208', '4209', '4210', '4211', '4212', '4304', '4305', '4306', '4307', '4308', '4866', '4867', '4868', '4869', '4870', '4871', '4872', '4873', '4874', '4875')) GROUP BY `ds`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6_md5`, `p7_md5` ORDER BY `ds2` DESC; 107 id estRows task access object operator info 108 Projection_7 53.00 root test.dt.ds, test.dt.p1, test.dt.p2, test.dt.p3, test.dt.p4, test.dt.p5, test.dt.p6_md5, test.dt.p7_md5, DeferredCauset#21 109 └─Sort_8 53.00 root test.dt.ds2:desc 110 └─HashAgg_16 53.00 root group by:test.dt.ds, test.dt.p1, test.dt.p2, test.dt.p3, test.dt.p4, test.dt.p5, test.dt.p6_md5, test.dt.p7_md5, funcs:count(DeferredCauset#32)->DeferredCauset#21, funcs:firstrow(test.dt.ds)->test.dt.ds, funcs:firstrow(DeferredCauset#34)->test.dt.ds2, funcs:firstrow(test.dt.p1)->test.dt.p1, funcs:firstrow(test.dt.p2)->test.dt.p2, funcs:firstrow(test.dt.p3)->test.dt.p3, funcs:firstrow(test.dt.p4)->test.dt.p4, funcs:firstrow(test.dt.p5)->test.dt.p5, funcs:firstrow(test.dt.p6_md5)->test.dt.p6_md5, funcs:firstrow(test.dt.p7_md5)->test.dt.p7_md5 111 └─IndexLookUp_17 53.00 root 112 ├─IndexRangeScan_13(Build) 2650.00 cop[einsteindb] causet:dt, index:cmi(cm) range:[1062,1062], [1086,1086], [1423,1423], [1424,1424], [1425,1425], [1426,1426], [1427,1427], [1428,1428], [1429,1429], [1430,1430], [1431,1431], [1432,1432], [1433,1433], [1434,1434], [1435,1435], [1436,1436], [1437,1437], [1438,1438], [1439,1439], [1440,1440], [1441,1441], [1442,1442], [1443,1443], [1444,1444], [1445,1445], [1446,1446], [1447,1447], [1448,1448], [1449,1449], [1450,1450], [1451,1451], [1452,1452], [1488,1488], [1489,1489], [1490,1490], [1491,1491], [1492,1492], [1493,1493], [1494,1494], [1495,1495], [1496,1496], [1497,1497], [1550,1550], [1551,1551], [1552,1552], [1553,1553], [1554,1554], [1555,1555], [1556,1556], [1557,1557], [1558,1558], [1559,1559], [1597,1597], [1598,1598], [1599,1599], [1600,1600], [1601,1601], [1602,1602], [1603,1603], [1604,1604], [1605,1605], [1606,1606], [1607,1607], [1608,1608], [1609,1609], [1610,1610], [1611,1611], [1612,1612], [1613,1613], [1614,1614], [1615,1615], [1616,1616], [1623,1623], [1624,1624], [1625,1625], [1626,1626], [1627,1627], [1628,1628], [1629,1629], [1630,1630], [1631,1631], [1632,1632], [1709,1709], [1719,1719], [1720,1720], [1843,1843], [2813,2813], [2814,2814], [2815,2815], [2816,2816], [2817,2817], [2818,2818], [2819,2819], [2820,2820], [2821,2821], [2822,2822], [2823,2823], [2824,2824], [2825,2825], [2826,2826], [2827,2827], [2828,2828], [2829,2829], [2830,2830], [2831,2831], [2832,2832], [2833,2833], [2834,2834], [2835,2835], [2836,2836], [2837,2837], [2838,2838], [2839,2839], [2840,2840], [2841,2841], [2842,2842], [2843,2843], [2844,2844], [2845,2845], [2846,2846], [2847,2847], [2848,2848], [2849,2849], [2850,2850], [2851,2851], [2852,2852], [2853,2853], [2854,2854], [2855,2855], [2856,2856], [2857,2857], [2858,2858], [2859,2859], [2860,2860], [2861,2861], [2862,2862], [2863,2863], [2864,2864], [2865,2865], [2866,2866], [2867,2867], [2868,2868], [2869,2869], [2870,2870], [2871,2871], [2872,2872], [3139,3139], [3140,3140], [3141,3141], [3142,3142], [3143,3143], [3144,3144], [3145,3145], [3146,3146], [3147,3147], [3148,3148], [3149,3149], [3150,3150], [3151,3151], [3152,3152], [3153,3153], [3154,3154], [3155,3155], [3156,3156], [3157,3157], [3158,3158], [3386,3386], [3387,3387], [3388,3388], [3389,3389], [3390,3390], [3391,3391], [3392,3392], [3393,3393], [3394,3394], [3395,3395], [3664,3664], [3665,3665], [3666,3666], [3667,3667], [3668,3668], [3670,3670], [3671,3671], [3672,3672], [3673,3673], [3674,3674], [3676,3676], [3677,3677], [3678,3678], [3679,3679], [3680,3680], [3681,3681], [3682,3682], [3683,3683], [3684,3684], [3685,3685], [3686,3686], [3687,3687], [3688,3688], [3689,3689], [3690,3690], [3691,3691], [3692,3692], [3693,3693], [3694,3694], [3695,3695], [3696,3696], [3697,3697], [3698,3698], [3699,3699], [3700,3700], [3701,3701], [3702,3702], [3703,3703], [3704,3704], [3705,3705], [3706,3706], [3707,3707], [3708,3708], [3709,3709], [3710,3710], [3711,3711], [3712,3712], [3713,3713], [3714,3714], [3715,3715], [3960,3960], [3961,3961], [3962,3962], [3963,3963], [3964,3964], [3965,3965], [3966,3966], [3967,3967], [3968,3968], [3978,3978], [3979,3979], [3980,3980], [3981,3981], [3982,3982], [3983,3983], [3984,3984], [3985,3985], [3986,3986], [3987,3987], [4208,4208], [4209,4209], [4210,4210], [4211,4211], [4212,4212], [4304,4304], [4305,4305], [4306,4306], [4307,4307], [4308,4308], [4866,4866], [4867,4867], [4868,4868], [4869,4869], [4870,4870], [4871,4871], [4872,4872], [4873,4873], [4874,4874], [4875,4875], keep order:false, stats:pseudo 113 └─HashAgg_11(Probe) 53.00 cop[einsteindb] group by:test.dt.ds, test.dt.p1, test.dt.p2, test.dt.p3, test.dt.p4, test.dt.p5, test.dt.p6_md5, test.dt.p7_md5, funcs:count(test.dt.dic)->DeferredCauset#32, funcs:firstrow(test.dt.ds2)->DeferredCauset#34 114 └─Selection_15 66.25 cop[einsteindb] ge(test.dt.ds, 2020-09-01 00:00:00.000000), le(test.dt.ds, 2020-11-03 00:00:00.000000) 115 └─TableRowIDScan_14 2650.00 cop[einsteindb] causet:dt keep order:false, stats:pseudo 116 explain select gad.id as gid,sdk.id as sid,gad.aid as aid,gad.cm as cm,sdk.dic as dic,sdk.ip as ip, sdk.t as t, gad.p1 as p1, gad.p2 as p2, gad.p3 as p3, gad.p4 as p4, gad.p5 as p5, gad.p6_md5 as p6, gad.p7_md5 as p7, gad.ext as ext, gad.t as gtime from st gad join (select id, aid, pt, dic, ip, t from dd where pt = 'android' and bm = 0 and t > 1478143908) sdk on gad.aid = sdk.aid and gad.ip = sdk.ip and sdk.t > gad.t where gad.t > 1478143908 and gad.pt = 'android' group by gad.aid, sdk.dic limit 2500; 117 id estRows task access object operator info 118 Projection_13 1.00 root test.st.id, test.dd.id, test.st.aid, test.st.cm, test.dd.dic, test.dd.ip, test.dd.t, test.st.p1, test.st.p2, test.st.p3, test.st.p4, test.st.p5, test.st.p6_md5, test.st.p7_md5, test.st.ext, test.st.t 119 └─Limit_16 1.00 root offset:0, count:2500 120 └─HashAgg_19 1.00 root group by:test.dd.dic, test.st.aid, funcs:firstrow(test.st.id)->test.st.id, funcs:firstrow(test.st.aid)->test.st.aid, funcs:firstrow(test.st.cm)->test.st.cm, funcs:firstrow(test.st.p1)->test.st.p1, funcs:firstrow(test.st.p2)->test.st.p2, funcs:firstrow(test.st.p3)->test.st.p3, funcs:firstrow(test.st.p4)->test.st.p4, funcs:firstrow(test.st.p5)->test.st.p5, funcs:firstrow(test.st.p6_md5)->test.st.p6_md5, funcs:firstrow(test.st.p7_md5)->test.st.p7_md5, funcs:firstrow(test.st.ext)->test.st.ext, funcs:firstrow(test.st.t)->test.st.t, funcs:firstrow(test.dd.id)->test.dd.id, funcs:firstrow(test.dd.dic)->test.dd.dic, funcs:firstrow(test.dd.ip)->test.dd.ip, funcs:firstrow(test.dd.t)->test.dd.t 121 └─HashJoin_34 0.00 root inner join, equal:[eq(test.dd.aid, test.st.aid) eq(test.dd.ip, test.st.ip)], other cond:gt(test.dd.t, test.st.t) 122 ├─IndexLookUp_52(Build) 0.00 root 123 │ ├─IndexRangeScan_49(Build) 3333.33 cop[einsteindb] causet:dd, index:t(t) range:(1478143908,+inf], keep order:false, stats:pseudo 124 │ └─Selection_51(Probe) 0.00 cop[einsteindb] eq(test.dd.bm, 0), eq(test.dd.pt, "android"), not(isnull(test.dd.ip)) 125 │ └─TableRowIDScan_50 3333.33 cop[einsteindb] causet:dd keep order:false, stats:pseudo 126 └─IndexLookUp_41(Probe) 3.33 root 127 ├─IndexRangeScan_38(Build) 3333.33 cop[einsteindb] causet:gad, index:t(t) range:(1478143908,+inf], keep order:false, stats:pseudo 128 └─Selection_40(Probe) 3.33 cop[einsteindb] eq(test.st.pt, "android"), not(isnull(test.st.ip)) 129 └─TableRowIDScan_39 3333.33 cop[einsteindb] causet:gad keep order:false, stats:pseudo 130 explain select gad.id as gid,sdk.id as sid,gad.aid as aid,gad.cm as cm,sdk.dic as dic,sdk.ip as ip, sdk.t as t, gad.p1 as p1, gad.p2 as p2, gad.p3 as p3, gad.p4 as p4, gad.p5 as p5, gad.p6_md5 as p6, gad.p7_md5 as p7, gad.ext as ext from st gad join dd sdk on gad.aid = sdk.aid and gad.dic = sdk.mac and gad.t < sdk.t where gad.t > 1477971479 and gad.bm = 0 and gad.pt = 'ios' and gad.dit = 'mac' and sdk.t > 1477971479 and sdk.bm = 0 and sdk.pt = 'ios' limit 3000; 131 id estRows task access object operator info 132 Projection_10 0.00 root test.st.id, test.dd.id, test.st.aid, test.st.cm, test.dd.dic, test.dd.ip, test.dd.t, test.st.p1, test.st.p2, test.st.p3, test.st.p4, test.st.p5, test.st.p6_md5, test.st.p7_md5, test.st.ext 133 └─Limit_13 0.00 root offset:0, count:3000 134 └─IndexMergeJoin_26 0.00 root inner join, inner:IndexLookUp_24, outer key:test.st.aid, inner key:test.dd.aid, other cond:eq(test.st.dic, test.dd.mac), lt(test.st.t, test.dd.t) 135 ├─IndexLookUp_35(Build) 0.00 root 136 │ ├─IndexRangeScan_32(Build) 3333.33 cop[einsteindb] causet:gad, index:t(t) range:(1477971479,+inf], keep order:false, stats:pseudo 137 │ └─Selection_34(Probe) 0.00 cop[einsteindb] eq(test.st.bm, 0), eq(test.st.dit, "mac"), eq(test.st.pt, "ios"), not(isnull(test.st.dic)) 138 │ └─TableRowIDScan_33 3333.33 cop[einsteindb] causet:gad keep order:false, stats:pseudo 139 └─IndexLookUp_24(Probe) 0.00 root 140 ├─IndexRangeScan_21(Build) 10000.00 cop[einsteindb] causet:sdk, index:aid(aid, dic) range: decided by [eq(test.dd.aid, test.st.aid)], keep order:true, stats:pseudo 141 └─Selection_23(Probe) 0.00 cop[einsteindb] eq(test.dd.bm, 0), eq(test.dd.pt, "ios"), gt(test.dd.t, 1477971479), not(isnull(test.dd.mac)), not(isnull(test.dd.t)) 142 └─TableRowIDScan_22 10000.00 cop[einsteindb] causet:sdk keep order:false, stats:pseudo 143 explain SELECT cm, p1, p2, p3, p4, p5, p6_md5, p7_md5, count(1) as click_pv, count(DISTINCT ip) as click_ip FROM st WHERE (t between 1478188800 and 1478275200) and aid='cn.sbkcq' and pt='android' GROUP BY cm, p1, p2, p3, p4, p5, p6_md5, p7_md5; 144 id estRows task access object operator info 145 Projection_5 1.00 root test.st.cm, test.st.p1, test.st.p2, test.st.p3, test.st.p4, test.st.p5, test.st.p6_md5, test.st.p7_md5, DeferredCauset#20, DeferredCauset#21 146 └─HashAgg_6 1.00 root group by:test.st.cm, test.st.p1, test.st.p2, test.st.p3, test.st.p4, test.st.p5, test.st.p6_md5, test.st.p7_md5, funcs:count(1)->DeferredCauset#20, funcs:count(distinct test.st.ip)->DeferredCauset#21, funcs:firstrow(test.st.cm)->test.st.cm, funcs:firstrow(test.st.p1)->test.st.p1, funcs:firstrow(test.st.p2)->test.st.p2, funcs:firstrow(test.st.p3)->test.st.p3, funcs:firstrow(test.st.p4)->test.st.p4, funcs:firstrow(test.st.p5)->test.st.p5, funcs:firstrow(test.st.p6_md5)->test.st.p6_md5, funcs:firstrow(test.st.p7_md5)->test.st.p7_md5 147 └─IndexLookUp_13 0.00 root 148 ├─IndexRangeScan_10(Build) 250.00 cop[einsteindb] causet:st, index:t(t) range:[1478188800,1478275200], keep order:false, stats:pseudo 149 └─Selection_12(Probe) 0.00 cop[einsteindb] eq(test.st.aid, "cn.sbkcq"), eq(test.st.pt, "android") 150 └─TableRowIDScan_11 250.00 cop[einsteindb] causet:st keep order:false, stats:pseudo 151 explain select dt.id as id, dt.aid as aid, dt.pt as pt, dt.dic as dic, dt.cm as cm, rr.gid as gid, rr.acd as acd, rr.t as t,dt.p1 as p1, dt.p2 as p2, dt.p3 as p3, dt.p4 as p4, dt.p5 as p5, dt.p6_md5 as p6, dt.p7_md5 as p7 from dt dt join rr rr on (rr.pt = 'ios' and rr.t > 1478185592 and dt.aid = rr.aid and dt.dic = rr.dic) where dt.pt = 'ios' and dt.t > 1478185592 and dt.bm = 0 limit 2000; 152 id estRows task access object operator info 153 Projection_10 0.00 root test.dt.id, test.dt.aid, test.dt.pt, test.dt.dic, test.dt.cm, test.rr.gid, test.rr.acd, test.rr.t, test.dt.p1, test.dt.p2, test.dt.p3, test.dt.p4, test.dt.p5, test.dt.p6_md5, test.dt.p7_md5 154 └─Limit_13 0.00 root offset:0, count:2000 155 └─IndexMergeJoin_43 0.00 root inner join, inner:IndexLookUp_41, outer key:test.rr.aid, test.rr.dic, inner key:test.dt.aid, test.dt.dic 156 ├─TableReader_61(Build) 3.33 root data:Selection_60 157 │ └─Selection_60 3.33 cop[einsteindb] eq(test.rr.pt, "ios"), gt(test.rr.t, 1478185592) 158 │ └─TableFullScan_59 10000.00 cop[einsteindb] causet:rr keep order:false, stats:pseudo 159 └─IndexLookUp_41(Probe) 0.00 root 160 ├─Selection_39(Build) 1.00 cop[einsteindb] not(isnull(test.dt.dic)) 161 │ └─IndexRangeScan_37 1.00 cop[einsteindb] causet:dt, index:aid(aid, dic) range: decided by [eq(test.dt.aid, test.rr.aid) eq(test.dt.dic, test.rr.dic)], keep order:true, stats:pseudo 162 └─Selection_40(Probe) 0.00 cop[einsteindb] eq(test.dt.bm, 0), eq(test.dt.pt, "ios"), gt(test.dt.t, 1478185592) 163 └─TableRowIDScan_38 1.00 cop[einsteindb] causet:dt keep order:false, stats:pseudo 164 explain select pc,cr,count(DISTINCT uid) as pay_users,count(oid) as pay_times,sum(am) as am from pp where ps=2 and ppt>=1478188800 and ppt<1478275200 and pi in ('510017','520017') and uid in ('18089709','18090780') group by pc,cr; 165 id estRows task access object operator info 166 Projection_5 1.00 root test.pp.pc, test.pp.cr, DeferredCauset#22, DeferredCauset#23, DeferredCauset#24 167 └─HashAgg_6 1.00 root group by:test.pp.cr, test.pp.pc, funcs:count(distinct test.pp.uid)->DeferredCauset#22, funcs:count(test.pp.oid)->DeferredCauset#23, funcs:sum(test.pp.am)->DeferredCauset#24, funcs:firstrow(test.pp.pc)->test.pp.pc, funcs:firstrow(test.pp.cr)->test.pp.cr 168 └─IndexLookUp_21 0.00 root 169 ├─IndexRangeScan_18(Build) 0.40 cop[einsteindb] causet:pp, index:sp(uid, pi) range:[18089709 510017,18089709 510017], [18089709 520017,18089709 520017], [18090780 510017,18090780 510017], [18090780 520017,18090780 520017], keep order:false, stats:pseudo 170 └─Selection_20(Probe) 0.00 cop[einsteindb] eq(test.pp.ps, 2), ge(test.pp.ppt, 1478188800), lt(test.pp.ppt, 1478275200) 171 └─TableRowIDScan_19 0.40 cop[einsteindb] causet:pp keep order:false, stats:pseudo 172 CREATE TABLE `tbl_001` (`a` int, `b` int); 173 CREATE TABLE `tbl_002` (`a` int, `b` int); 174 CREATE TABLE `tbl_003` (`a` int, `b` int); 175 CREATE TABLE `tbl_004` (`a` int, `b` int); 176 CREATE TABLE `tbl_005` (`a` int, `b` int); 177 CREATE TABLE `tbl_006` (`a` int, `b` int); 178 CREATE TABLE `tbl_007` (`a` int, `b` int); 179 CREATE TABLE `tbl_008` (`a` int, `b` int); 180 CREATE TABLE `tbl_009` (`a` int, `b` int); 181 explain select sum(a) from (select * from tbl_001 union all select * from tbl_002 union all select * from tbl_003 union all select * from tbl_004 union all select * from tbl_005 union all select * from tbl_006 union all select * from tbl_007 union all select * from tbl_008 union all select * from tbl_009) x group by b; 182 id estRows task access object operator info 183 HashAgg_34 72000.00 root group by:DeferredCauset#32, funcs:sum(DeferredCauset#31)->DeferredCauset#30 184 └─Projection_63 90000.00 root cast(DeferredCauset#28, decimal(65,0) BINARY)->DeferredCauset#31, DeferredCauset#29 185 └─Union_35 90000.00 root 186 ├─TableReader_38 10000.00 root data:TableFullScan_37 187 │ └─TableFullScan_37 10000.00 cop[einsteindb] causet:tbl_001 keep order:false, stats:pseudo 188 ├─TableReader_41 10000.00 root data:TableFullScan_40 189 │ └─TableFullScan_40 10000.00 cop[einsteindb] causet:tbl_002 keep order:false, stats:pseudo 190 ├─TableReader_44 10000.00 root data:TableFullScan_43 191 │ └─TableFullScan_43 10000.00 cop[einsteindb] causet:tbl_003 keep order:false, stats:pseudo 192 ├─TableReader_47 10000.00 root data:TableFullScan_46 193 │ └─TableFullScan_46 10000.00 cop[einsteindb] causet:tbl_004 keep order:false, stats:pseudo 194 ├─TableReader_50 10000.00 root data:TableFullScan_49 195 │ └─TableFullScan_49 10000.00 cop[einsteindb] causet:tbl_005 keep order:false, stats:pseudo 196 ├─TableReader_53 10000.00 root data:TableFullScan_52 197 │ └─TableFullScan_52 10000.00 cop[einsteindb] causet:tbl_006 keep order:false, stats:pseudo 198 ├─TableReader_56 10000.00 root data:TableFullScan_55 199 │ └─TableFullScan_55 10000.00 cop[einsteindb] causet:tbl_007 keep order:false, stats:pseudo 200 ├─TableReader_59 10000.00 root data:TableFullScan_58 201 │ └─TableFullScan_58 10000.00 cop[einsteindb] causet:tbl_008 keep order:false, stats:pseudo 202 └─TableReader_62 10000.00 root data:TableFullScan_61 203 └─TableFullScan_61 10000.00 cop[einsteindb] causet:tbl_009 keep order:false, stats:pseudo 204 CREATE TABLE org_department ( 205 id int(11) NOT NULL AUTO_INCREMENT, 206 ctx int(11) DEFAULT '0' COMMENT 'organization id', 207 name varchar(128) DEFAULT NULL, 208 left_value int(11) DEFAULT NULL, 209 right_value int(11) DEFAULT NULL, 210 depth int(11) DEFAULT NULL, 211 leader_id bigint(20) DEFAULT NULL, 212 status int(11) DEFAULT '1000', 213 created_on datetime DEFAULT NULL, 214 uFIDelated_on datetime DEFAULT NULL, 215 PRIMARY KEY (id), 216 UNIQUE KEY org_department_id_uindex (id), 217 KEY org_department_leader_id_index (leader_id), 218 KEY org_department_ctx_index (ctx) 219 ); 220 CREATE TABLE org_position ( 221 id int(11) NOT NULL AUTO_INCREMENT, 222 ctx int(11) DEFAULT NULL, 223 name varchar(128) DEFAULT NULL, 224 left_value int(11) DEFAULT NULL, 225 right_value int(11) DEFAULT NULL, 226 depth int(11) DEFAULT NULL, 227 department_id int(11) DEFAULT NULL, 228 status int(2) DEFAULT NULL, 229 created_on datetime DEFAULT NULL, 230 uFIDelated_on datetime DEFAULT NULL, 231 PRIMARY KEY (id), 232 UNIQUE KEY org_position_id_uindex (id), 233 KEY org_position_department_id_index (department_id) 234 ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8; 235 CREATE TABLE org_employee_position ( 236 hotel_id int(11) DEFAULT NULL, 237 user_id bigint(20) DEFAULT NULL, 238 position_id int(11) DEFAULT NULL, 239 status int(11) DEFAULT NULL, 240 created_on datetime DEFAULT NULL, 241 uFIDelated_on datetime DEFAULT NULL, 242 UNIQUE KEY org_employee_position_pk (hotel_id,user_id,position_id) 243 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 244 explain SELECT d.id, d.ctx, d.name, d.left_value, d.right_value, d.depth, d.leader_id, d.status, d.created_on, d.uFIDelated_on FROM org_department AS d LEFT JOIN org_position AS p ON p.department_id = d.id AND p.status = 1000 LEFT JOIN org_employee_position AS ep ON ep.position_id = p.id AND ep.status = 1000 WHERE (d.ctx = 1 AND (ep.user_id = 62 OR d.id = 20 OR d.id = 20) AND d.status = 1000) GROUP BY d.id ORDER BY d.left_value; 245 id estRows task access object operator info 246 Sort_10 1.00 root test.org_department.left_value 247 └─HashAgg_15 1.00 root group by:test.org_department.id, funcs:firstrow(test.org_department.id)->test.org_department.id, funcs:firstrow(test.org_department.ctx)->test.org_department.ctx, funcs:firstrow(test.org_department.name)->test.org_department.name, funcs:firstrow(test.org_department.left_value)->test.org_department.left_value, funcs:firstrow(test.org_department.right_value)->test.org_department.right_value, funcs:firstrow(test.org_department.depth)->test.org_department.depth, funcs:firstrow(test.org_department.leader_id)->test.org_department.leader_id, funcs:firstrow(test.org_department.status)->test.org_department.status, funcs:firstrow(test.org_department.created_on)->test.org_department.created_on, funcs:firstrow(test.org_department.uFIDelated_on)->test.org_department.uFIDelated_on 248 └─Selection_22 0.01 root or(eq(test.org_employee_position.user_id, 62), or(eq(test.org_department.id, 20), eq(test.org_department.id, 20))) 249 └─HashJoin_24 0.02 root left outer join, equal:[eq(test.org_position.id, test.org_employee_position.position_id)] 250 ├─IndexMergeJoin_42(Build) 0.01 root left outer join, inner:IndexLookUp_40, outer key:test.org_department.id, inner key:test.org_position.department_id 251 │ ├─IndexLookUp_62(Build) 0.01 root 252 │ │ ├─IndexRangeScan_59(Build) 10.00 cop[einsteindb] causet:d, index:org_department_ctx_index(ctx) range:[1,1], keep order:false, stats:pseudo 253 │ │ └─Selection_61(Probe) 0.01 cop[einsteindb] eq(test.org_department.status, 1000) 254 │ │ └─TableRowIDScan_60 10.00 cop[einsteindb] causet:d keep order:false, stats:pseudo 255 │ └─IndexLookUp_40(Probe) 1.25 root 256 │ ├─Selection_38(Build) 1250.00 cop[einsteindb] not(isnull(test.org_position.department_id)) 257 │ │ └─IndexRangeScan_36 1251.25 cop[einsteindb] causet:p, index:org_position_department_id_index(department_id) range: decided by [eq(test.org_position.department_id, test.org_department.id)], keep order:true, stats:pseudo 258 │ └─Selection_39(Probe) 1.25 cop[einsteindb] eq(test.org_position.status, 1000) 259 │ └─TableRowIDScan_37 1250.00 cop[einsteindb] causet:p keep order:false, stats:pseudo 260 └─TableReader_72(Probe) 9.99 root data:Selection_71 261 └─Selection_71 9.99 cop[einsteindb] eq(test.org_employee_position.status, 1000), not(isnull(test.org_employee_position.position_id)) 262 └─TableFullScan_70 10000.00 cop[einsteindb] causet:ep keep order:false, stats:pseudo