Gabriel Harper's Blog

I was recently using phpMyAdmin to export some records from an URL shortener I’m working on. As usual I verified that “Complete inserts” and “Extended inserts” were enabled for the SQL dump. Why? Using both of these options reduces your SQL filesize and explicitly declares each column name.

Complete inserts adds the column names to the SQL dump. This parameter improves the readability and reliability of the dump. Adding the column names increases the size of the dump, but when combined with Extended inserts it’s negligible.

Extended inserts combines multiple rows of data into a single INSERT query. This will significantly decrease filesize for large SQL dumps, increases the INSERT speed when imported, and is generally recommended.

The following examples show the differences exporting a table with 3 rows.

Both options unchecked:

INSERT INTO `table` VALUES(1, 'Bob');
INSERT INTO `table` VALUES(2, 'Brad');
INSERT INTO `table` VALUES(3, 'Ben');

Complete inserts only:

INSERT INTO `table` (`id`, `name`) VALUES(1, 'Bob');
INSERT INTO `table` (`id`, `name`) VALUES(2, 'Brad');
INSERT INTO `table` (`id`, `name`) VALUES(3, 'Ben');

Extended inserts only:

INSERT INTO `table` VALUES
(1, 'Bob'),
(2, 'Brad'),
(3, 'Ben');

Complete inserts AND Extended inserts:

INSERT INTO `table` (`id`, `name`) VALUES
(1, 'Bob'),
(2, 'Brad'),
(3, 'Ben');

As you can see, combining both parameters gives the best balance of SQL dump size and readability.

When you export data with phpMyAdmin it uses the mysqldump command built in to MySQL. The Extended inserts and Complete inserts checkboxes in the Data section when dumping a table actually set two parameters in mysqldump: –extended-insert and –complete-insert. These parameters are not mutually exclusive – you can use one, both, or neither. There is no “complete inserts vs. extended inserts” argument. They each serve a different function.

This entry was posted on Sunday, April 12th, 2009 at 9:57 am and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.