github.com/dolthub/go-mysql-server@v0.18.0/enginetest/scriptgen/setup/scripts/join (about) 1 exec 2 create table asset ( 3 id int primary key, 4 orgId varchar(10), 5 assetId varchar(10), 6 name varchar(20), 7 val varchar(20), 8 key idx1 (orgId, assetId), 9 unique key (orgId, name, assetId), 10 key (orgId, name, val) 11 ); 12 ---- 13 14 exec 15 insert into asset values 16 (0, 'org1', 'small', 'style', 'curve'), 17 (1, 'org1', 'small', 'dimension', 'wide'), 18 (2, 'org1', 'small', 'color', 'blue'), 19 (3, 'org1', 'small', 'retries', 'curve'), 20 (4, 'org1', 'medium', 'style', 'straight'), 21 (5, 'org1', 'medium', 'color', 'green'), 22 (6, 'org1', 'medium', 'dimension', 'narrow'), 23 (7, 'org1', 'medium', 'retries', 'straight'), 24 (8, 'org1', 'large', 'style', 'bendy'), 25 (9, 'org1', 'large', 'color', 'red'), 26 (10, 'org1', 'large', 'dimension', 'round'), 27 (11, 'org1', 'large', 'retries', 'bendy'); 28 ---- 29 30 exec 31 CREATE TABLE `warehouse1` ( 32 `w_id` smallint NOT NULL, 33 `w_name` varchar(10), 34 `w_street_1` varchar(20), 35 `w_street_2` varchar(20), 36 `w_city` varchar(20), 37 `w_state` char(2), 38 `w_zip` char(9), 39 `w_tax` decimal(4,2), 40 `w_ytd` decimal(12,2), 41 PRIMARY KEY (`w_id`) 42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; 43 ---- 44 45 exec 46 CREATE TABLE `district1` ( 47 `d_id` tinyint NOT NULL, 48 `d_w_id` smallint NOT NULL, 49 `d_name` varchar(10), 50 `d_street_1` varchar(20), 51 `d_street_2` varchar(20), 52 `d_city` varchar(20), 53 `d_state` char(2), 54 `d_zip` char(9), 55 `d_tax` decimal(4,2), 56 `d_ytd` decimal(12,2), 57 `d_next_o_id` int, 58 PRIMARY KEY (`d_w_id`,`d_id`), 59 CONSTRAINT `fkey_district_1_1` FOREIGN KEY (`d_w_id`) REFERENCES `warehouse1` (`w_id`) 60 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; 61 ---- 62 63 exec 64 CREATE TABLE `item1` ( 65 `i_id` int NOT NULL, 66 `i_im_id` int, 67 `i_name` varchar(24), 68 `i_price` decimal(5,2), 69 `i_data` varchar(50), 70 PRIMARY KEY (`i_id`) 71 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; 72 ---- 73 74 exec 75 CREATE TABLE `customer1` ( 76 `c_id` int NOT NULL, 77 `c_d_id` tinyint NOT NULL, 78 `c_w_id` smallint NOT NULL, 79 `c_first` varchar(16), 80 `c_middle` char(2), 81 `c_last` varchar(16), 82 `c_street_1` varchar(20), 83 `c_street_2` varchar(20), 84 `c_city` varchar(20), 85 `c_state` char(2), 86 `c_zip` char(9), 87 `c_phone` char(16), 88 `c_since` datetime(6), 89 `c_credit` char(2), 90 `c_credit_lim` bigint, 91 `c_discount` decimal(4,2), 92 `c_balance` decimal(12,2), 93 `c_ytd_payment` decimal(12,2), 94 `c_payment_cnt` smallint, 95 `c_delivery_cnt` smallint, 96 `c_data` text, 97 PRIMARY KEY (`c_w_id`,`c_d_id`,`c_id`), 98 KEY `idx_customer1` (`c_w_id`,`c_d_id`,`c_last`,`c_first`), 99 CONSTRAINT `fkey_customer_1_1` FOREIGN KEY (`c_w_id`,`c_d_id`) REFERENCES `district1` (`d_w_id`,`d_id`) 100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; 101 --- 102 103 exec 104 CREATE TABLE `orders1` ( 105 `o_id` int NOT NULL, 106 `o_d_id` tinyint NOT NULL, 107 `o_w_id` smallint NOT NULL, 108 `o_c_id` int, 109 `o_entry_d` datetime(6), 110 `o_carrier_id` tinyint, 111 `o_ol_cnt` tinyint, 112 `o_all_local` tinyint, 113 PRIMARY KEY (`o_w_id`,`o_d_id`,`o_id`), 114 KEY `idx_orders1` (`o_w_id`,`o_d_id`,`o_c_id`,`o_id`), 115 CONSTRAINT `fkey_orders_1_1` FOREIGN KEY (`o_w_id`,`o_d_id`,`o_c_id`) REFERENCES `customer1` (`c_w_id`,`c_d_id`,`c_id`) 116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; 117 ---- 118 119 exec 120 CREATE TABLE `stock1` ( 121 `s_i_id` int NOT NULL, 122 `s_w_id` smallint NOT NULL, 123 `s_quantity` smallint, 124 `s_dist_01` char(24), 125 `s_dist_02` char(24), 126 `s_dist_03` char(24), 127 `s_dist_04` char(24), 128 `s_dist_05` char(24), 129 `s_dist_06` char(24), 130 `s_dist_07` char(24), 131 `s_dist_08` char(24), 132 `s_dist_09` char(24), 133 `s_dist_10` char(24), 134 `s_ytd` decimal(8,0), 135 `s_order_cnt` smallint, 136 `s_remote_cnt` smallint, 137 `s_data` varchar(50), 138 PRIMARY KEY (`s_w_id`,`s_i_id`), 139 KEY `fkey_stock_21` (`s_i_id`), 140 CONSTRAINT `fkey_stock_1_1` FOREIGN KEY (`s_w_id`) REFERENCES `warehouse1` (`w_id`), 141 CONSTRAINT `fkey_stock_2_1` FOREIGN KEY (`s_i_id`) REFERENCES `item1` (`i_id`) 142 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; 143 ---- 144 145 exec 146 CREATE TABLE `order_line1` ( 147 `ol_o_id` int NOT NULL, 148 `ol_d_id` tinyint NOT NULL, 149 `ol_w_id` smallint NOT NULL, 150 `ol_number` tinyint NOT NULL, 151 `ol_i_id` int, 152 `ol_supply_w_id` smallint, 153 `ol_delivery_d` datetime(6), 154 `ol_quantity` tinyint, 155 `ol_amount` decimal(6,2), 156 `ol_dist_info` char(24), 157 PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`), 158 KEY `fkey_order_line_21` (`ol_supply_w_id`,`ol_i_id`), 159 CONSTRAINT `fkey_order_line_1_1` FOREIGN KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`) REFERENCES `orders1` (`o_w_id`,`o_d_id`,`o_id`), 160 CONSTRAINT `fkey_order_line_2_1` FOREIGN KEY (`ol_supply_w_id`,`ol_i_id`) REFERENCES `stock1` (`s_w_id`,`s_i_id`) 161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin; 162 ---- 163 164 exec 165 analyze table customer1 update histogram on (`c_w_id`,`c_d_id`,`c_id`) using data '{\"row_count\": 300000}'; 166 ---- 167 168 exec 169 analyze table warehouse1 update histogram on (w_id) using data '{\"row_count\": 10}'; 170 ---- 171 172 exec 173 analyze table stock1 update histogram on (`s_w_id`,`s_i_id`) using data '{\"row_count\": 1000000}'; 174 ---- 175 176 exec 177 analyze table order_line1 update histogram on (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`) using data '{\"row_count\": 3006058}'; 178 ---- 179 180 exec 181 analyze table district1 update histogram on (`d_w_id`,`d_id`) using data '{\"row_count\": 100}'; 182 ---- 183 184 exec 185 analyze table item1 update histogram on (i_id) using data '{\"row_count\": 100000}'; 186 ---- 187 188 exec 189 analyze table orders1 update histogram on (`o_w_id`,`o_d_id`,`o_id`) using data '{\"row_count\": 300520}'; 190 ----