github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/t/explain_complex.test (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  
    28  CREATE TABLE `st` (
    29    `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
    30    `pt` varchar(10) NOT NULL ,
    31    `aid` varchar(35) NOT NULL ,
    32    `cm` int(10) NOT NULL ,
    33    `ip` varchar(15) DEFAULT NULL ,
    34    `dic` varchar(64) DEFAULT NULL ,
    35    `dit` varchar(5) DEFAULT NULL,
    36    `p1` varchar(64) DEFAULT NULL ,
    37    `p2` varchar(64) DEFAULT NULL,
    38    `p3` varchar(64) DEFAULT NULL,
    39    `p4` varchar(64) DEFAULT NULL,
    40    `p5` varchar(64) DEFAULT NULL,
    41    `p6_md5` varchar(32) DEFAULT NULL,
    42    `p7_md5` varchar(32) DEFAULT NULL,
    43    `ext` varchar(550) DEFAULT NULL,
    44    `bm` tinyint(1) DEFAULT '0',
    45    `ds` date NOT NULL ,
    46    `ds2` varchar(13) DEFAULT NULL ,
    47    `t` int(13) NOT NULL ,
    48   PRIMARY KEY (`id`),
    49    KEY `t` (`t`),
    50    KEY `icd` (`cm`,`ds`)
    51  );
    52  
    53  CREATE TABLE `dd` (
    54    `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
    55    `aid` varchar(35) NOT NULL ,
    56    `pt` varchar(10) NOT NULL ,
    57    `dic` varchar(64) NOT NULL,
    58    `dim` varchar(32) NOT NULL ,
    59    `mac` varchar(32) DEFAULT NULL ,
    60    `ip` varchar(15) DEFAULT NULL ,
    61    `t` int(13) DEFAULT NULL ,
    62    `bm` tinyint(1) DEFAULT '0',
    63   PRIMARY KEY (`id`),
    64    UNIQUE KEY `aid` (`aid`,`dic`),
    65    KEY `ip` (`ip`),
    66    KEY `pi` (`aid`,`dim`),
    67    KEY `t` (`t`)
    68  );
    69  
    70  CREATE TABLE `pp` (
    71    `oid` varchar(20) NOT NULL,
    72    `uid` bigint(20) unsigned NOT NULL,
    73    `cid` int(11) unsigned NOT NULL,
    74    `ppt` int(11) NOT NULL DEFAULT '0',
    75    `FIDelt` int(11) DEFAULT '0',
    76    `am` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
    77    `cc` decimal(10,2) NOT NULL DEFAULT '0.00',
    78    `ps` tinyint(1) NOT NULL,
    79    `tid` varchar(200) DEFAULT NULL,
    80    `ppf` varchar(50) NOT NULL,
    81    `bs` tinyint(1) NOT NULL DEFAULT '0',
    82    `bex` tinyint(1) NOT NULL DEFAULT '0',
    83    `bu` int(11) NOT NULL DEFAULT '0',
    84    `pc` char(10) NOT NULL DEFAULT 'CNY',
    85    `ui` int(16) NOT NULL DEFAULT '1',
    86    `cr` decimal(10,4) unsigned NOT NULL DEFAULT '1.0000',
    87    `pi` int(11) unsigned NOT NULL,
    88    `si` int(11) unsigned NOT NULL,
    89    `bcc` int(11) NOT NULL DEFAULT '0',
    90    `acc` int(11) NOT NULL DEFAULT '0',
    91    KEY `oid` (`oid`),
    92    KEY `uid` (`uid`),
    93    KEY `ppt` (`ppt`),
    94    KEY `FIDelt` (`FIDelt`),
    95    KEY `cid` (`cid`),
    96    KEY `ps` (`ps`),
    97    KEY `sp` (`uid`,`pi`)
    98  );
    99  
   100  CREATE TABLE `rr` (
   101    `aid` varchar(35) NOT NULL ,
   102    `pt` varchar(10) NOT NULL ,
   103    `dic` varchar(64) NOT NULL ,
   104    `gid` varchar(42) NOT NULL ,
   105    `acd` varchar(32) NOT NULL ,
   106    `t` int(13) DEFAULT NULL ,
   107    `bm` tinyint(1) DEFAULT '0',
   108    PRIMARY KEY (`aid`,`dic`)
   109  );
   110  
   111  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;
   112  
   113  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;
   114  
   115  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;
   116  
   117  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;
   118  
   119  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;
   120  
   121  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;
   122  
   123  CREATE TABLE `tbl_001` (`a` int, `b` int);
   124  CREATE TABLE `tbl_002` (`a` int, `b` int);
   125  CREATE TABLE `tbl_003` (`a` int, `b` int);
   126  CREATE TABLE `tbl_004` (`a` int, `b` int);
   127  CREATE TABLE `tbl_005` (`a` int, `b` int);
   128  CREATE TABLE `tbl_006` (`a` int, `b` int);
   129  CREATE TABLE `tbl_007` (`a` int, `b` int);
   130  CREATE TABLE `tbl_008` (`a` int, `b` int);
   131  CREATE TABLE `tbl_009` (`a` int, `b` int);
   132  
   133  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;
   134  
   135  CREATE TABLE org_department (
   136    id int(11) NOT NULL AUTO_INCREMENT,
   137    ctx int(11) DEFAULT '0' COMMENT 'organization id',
   138    name varchar(128) DEFAULT NULL,
   139    left_value int(11) DEFAULT NULL,
   140    right_value int(11) DEFAULT NULL,
   141    depth int(11) DEFAULT NULL,
   142    leader_id bigint(20) DEFAULT NULL,
   143    status int(11) DEFAULT '1000',
   144    created_on datetime DEFAULT NULL,
   145    uFIDelated_on datetime DEFAULT NULL,
   146    PRIMARY KEY (id),
   147    UNIQUE KEY org_department_id_uindex (id),
   148    KEY org_department_leader_id_index (leader_id),
   149    KEY org_department_ctx_index (ctx)
   150  );
   151  CREATE TABLE org_position (
   152    id int(11) NOT NULL AUTO_INCREMENT,
   153    ctx int(11) DEFAULT NULL,
   154    name varchar(128) DEFAULT NULL,
   155    left_value int(11) DEFAULT NULL,
   156    right_value int(11) DEFAULT NULL,
   157    depth int(11) DEFAULT NULL,
   158    department_id int(11) DEFAULT NULL,
   159    status int(2) DEFAULT NULL,
   160    created_on datetime DEFAULT NULL,
   161    uFIDelated_on datetime DEFAULT NULL,
   162    PRIMARY KEY (id),
   163    UNIQUE KEY org_position_id_uindex (id),
   164    KEY org_position_department_id_index (department_id)
   165  ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
   166    CREATE TABLE org_employee_position (
   167    hotel_id int(11) DEFAULT NULL,
   168    user_id bigint(20) DEFAULT NULL,
   169    position_id int(11) DEFAULT NULL,
   170    status int(11) DEFAULT NULL,
   171    created_on datetime DEFAULT NULL,
   172    uFIDelated_on datetime DEFAULT NULL,
   173    UNIQUE KEY org_employee_position_pk (hotel_id,user_id,position_id)
   174  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   175  
   176  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;