ajf6uCXq

1DROP TABLE IF EXISTS x;
2DROP TABLE IF EXISTS y;
3DROP TABLE IF EXISTS z;
4CREATE TABLE x (
5  id INT NOT NULL AUTO_INCREMENT,
6  v INT NOT NULL,
7  PRIMARY KEY (id)
8) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
9CREATE TABLE y (
10  id INT NOT NULL AUTO_INCREMENT,
11  v INT NOT NULL,
12  PRIMARY KEY (id)
13) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
14CREATE TABLE z (
15  id INT NOT NULL AUTO_INCREMENT,
16  v INT NOT NULL,
17  PRIMARY KEY (id)
18) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
19
20DELIMITER $$
21DROP PROCEDURE IF EXISTS createData$$
22CREATE PROCEDURE createData(size INT, count INT)
23BEGIN
24  SET @xs = 'INSERT INTO x(v) VALUES (FLOOR(RAND()*1000))';
25  SET @ys = 'INSERT INTO y(v) VALUES (FLOOR(RAND()*100000))';
26  SET @zs = 'INSERT INTO z(v) VALUES (FLOOR(RAND()*10000000))';
27  SET @i = 1;
28  WHILE @i < size DO
29    SET @xs = CONCAT(@xs, ',(FLOOR(RAND()*1000))');
30    SET @ys = CONCAT(@ys, ',(FLOOR(RAND()*100000))');
31    SET @zs = CONCAT(@zs, ',(FLOOR(RAND()*10000000))');
32    SET @i = @i + 1;
33  END WHILE;
34
35  PREPARE xstmt FROM @xs;
36  PREPARE ystmt FROM @ys;
37  PREPARE zstmt FROM @zs;
38  SET @i = 0;
39  WHILE @i < count DO
40    EXECUTE xstmt;
41    EXECUTE ystmt;
42    EXECUTE zstmt;
43    SET @i = @i + 1;
44  END WHILE;
45END;
46$$
47DELIMITER ;
48
49CALL createData(10000, 1000);
50ALTER TABLE x ADD KEY (v, id);
51ALTER TABLE y ADD KEY (v, id);
52ALTER TABLE z ADD KEY (v, id);
53ANALYZE TABLE x;
54ANALYZE TABLE y;
55ANALYZE TABLE z;