Description:
mysqldump doesn't generate sorted data by PK for some InnoDB tables.
So restoring the data will become very slow.
How to repeat:
CREATE TABLE `t` (
`id1` int(11) NOT NULL DEFAULT '0',
`id2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id1`,`id2`),
KEY `id2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
DROP PROCEDURE generate;
DELIMITER //
CREATE PROCEDURE generate()
BEGIN
DECLARE id1 INT;
DECLARE id2 INT;
SET id1 = 1;
WHILE id1 <= 10 DO
SET id2 = 1;
WHILE id2 <= 10 DO
INSERT INTO t (id1, id2) VALUES (id1, id2);
SET id2 = id2 + 1;
END WHILE;
SET id1 = id1 + 1;
END WHILE;
END
//
DELIMITER ;
$ mysqldump -u xxx -p xxx t > t.sql
<t.sql>
...
INSERT INTO `t` VALUES (1,1),(2,1),(3,1),(4,1),(5,1),
(6,1),(7,1),(8,1),(9,1),(10,1),(1,2),(2,2),
...
Suggested fix:
In this case, SQL Optimizer chooses the following plan.
But, in mysqldump, SQL Optimizer should choose a plan that uses PK.
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | index | NULL | id2 | 4 | NULL | 100 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
Idea 1:
mysqldump automatically adds --order-by-primary option or 'FORCE INDEX (PRMARY)' clause for InnoDB tables.
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t | index | NULL | PRIMARY | 8 | NULL | 100 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
Idea 2:
SQL Optimizer choose a plan that uses PK for such InnoDB tables.
Thanks,
Description: mysqldump doesn't generate sorted data by PK for some InnoDB tables. So restoring the data will become very slow. How to repeat: CREATE TABLE `t` ( `id1` int(11) NOT NULL DEFAULT '0', `id2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id1`,`id2`), KEY `id2` (`id2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DROP PROCEDURE generate; DELIMITER // CREATE PROCEDURE generate() BEGIN DECLARE id1 INT; DECLARE id2 INT; SET id1 = 1; WHILE id1 <= 10 DO SET id2 = 1; WHILE id2 <= 10 DO INSERT INTO t (id1, id2) VALUES (id1, id2); SET id2 = id2 + 1; END WHILE; SET id1 = id1 + 1; END WHILE; END // DELIMITER ; $ mysqldump -u xxx -p xxx t > t.sql <t.sql> ... INSERT INTO `t` VALUES (1,1),(2,1),(3,1),(4,1),(5,1), (6,1),(7,1),(8,1),(9,1),(10,1),(1,2),(2,2), ... Suggested fix: In this case, SQL Optimizer chooses the following plan. But, in mysqldump, SQL Optimizer should choose a plan that uses PK. +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t | index | NULL | id2 | 4 | NULL | 100 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ Idea 1: mysqldump automatically adds --order-by-primary option or 'FORCE INDEX (PRMARY)' clause for InnoDB tables. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | t | index | NULL | PRIMARY | 8 | NULL | 100 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ Idea 2: SQL Optimizer choose a plan that uses PK for such InnoDB tables. Thanks,