Posted 2009-04-12 in Scripts
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.
Subscribe via RSS
Stay current with Scriptalicious news and subscribe in your favorite RSS reader.
Get the latest Scriptalicious news delivered to your inbox.
77 powerful SEO scripts for under $100.
Real-time Backlink Rank Checker script.