Zd0FUEux

1DROP TABLE IF EXISTS t;
2CREATE TABLE t (
3  id INT NOT NULL AUTO_INCREMENT,
4  x INT NOT NULL,
5  y INT NOT NULL,
6  z INT NOT NULL,
7  PRIMARY KEY (id)
8) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
9
10DELIMITER $$
11DROP PROCEDURE IF EXISTS createData$$
12CREATE PROCEDURE createData(size INT, count INT)
13BEGIN
14  SET @s = 'INSERT INTO t(x,y,z) VALUES (FLOOR(RAND()*1000),FLOOR(RAND()*100000),FLOOR(RAND()*10000000))';
15  SET @i = 1;
16  WHILE @i < size DO
17    SET @s = CONCAT(@s, ',(FLOOR(RAND()*1000),FLOOR(RAND()*100000),FLOOR(RAND()*10000000))');
18    SET @i = @i + 1;
19  END WHILE;
20
21  PREPARE stmt FROM @s;
22  SET @i = 0;
23  WHILE @i < count DO
24    EXECUTE stmt;
25    SET @i = @i + 1;
26  END WHILE;
27END;
28$$
29DELIMITER ;
30
31CALL createData(10000, 1000);
32ALTER TABLE t ADD KEY (x, id), ADD KEY (y, id), ADD KEY (z, id);
33ANALYZE TABLE t;