Why is my MySQL dump file and my phpMyadmin export file different sizes?

I am looking to create a cron job to backup my MySQL database. I am using this command:

$ mysqldump --user="User" --password="Password" -A 
    --single-transaction > /home/user/Export-`date +"%F"`.sql

This results in a 2.5 MB file size. However, when I use the phpMyAdmin export feature using the same user, I get a 3.7 MB file.

Can someone please explain the differences, and if I'm doing anything wrong?

DISCLAIMER : Not a User of PhpMyAdmin

I can explain what mysqldump does. When you run mysqldump, only data is dumped in SQL statements. Indexed are not copied. Indexes get rebuilt when the SQL is loaded into another server.

Here is an example

I have a query I run to show index and data usage.

Here is that query and its output

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
    -> CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
    -> (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
    -> SUM(data_length+index_length) TSize FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('mysql','information_schema','performance_schema') AND
    -> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
    -> (SELECT 2 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| BLACKHOLE      |             0.000 MB |             0.000 MB |             0.000 MB |
| MEMORY         |             0.000 MB |             0.000 MB |             0.000 MB |
| FEDERATED      |             0.142 MB |             0.000 MB |             0.142 MB |
| MyISAM         |             8.049 MB |             0.368 MB |             8.417 MB |
| InnoDB         |            10.828 MB |             6.766 MB |            17.594 MB |
| Total          |            19.019 MB |             7.134 MB |            26.153 MB |
+----------------+----------------------+----------------------+----------------------+
6 rows in set (0.62 sec)

mysql>

Notice that my PC has 19.019 MB of data and 7.134 MB. mysqldump will only dump the 19.019 MB of data. The indexes are not exported.

If phpmyadmin uses mysqldump, then I only have one possible explanation. mysqldump uses the option --opt by default. Here is is what --opt does

--opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.

If phpmyadmin summons mysqldump using --skip-opt, then notice --extended-insert is not enabled. With --extended-insert enabled, a batch of rows is inserted using INSERT INTO. Using --skip-opt or --skip-extended-insert will cause each row in every table to have a separate INSERT INTO. Consequently, this would blow up the size of the SQL script. Subsequently, a reload would be longer than usual.

StackExchange Database Administrators Q#13122, answer score: 5