Why does PhpMyAdmin put the indexes at the end of a dump?

Question

When I export a MySQL database with Adminer or with mysqldump, it produces a .sql file with each table definition followed by (if the table is non-empty) an INSERT statement (or multiple statements for large tables). The table definition includes indexes and constraints. It will also include the AUTO_INCREMENT value if that is different from what you'd get after running the inserts.

By contrast, a PhpMyAdmin export defines the table without any indexes or constraints, and then inserts the data. At the end, after all tables have been created and all data has been inserted, are a bunch of ALTER TABLE commands to add keys, then a further set to add AUTO_INCREMENT rules, and finally a further set to add constraints.

Why the difference? Both export styles will produce the same database in the end. I assume that PhpMyAdmin made a deliberate, intentional choice to deviate from the mysqldump style. In my subjective experience (I've not actually measured) PhpMyAdmin dumps are slower to run than Adminer or mysqldump dumps. I imagine that they must have some benefit. What?

I might guess that if you're running a PhpMyAdmin dump on an older version of MySQL which doesn't understand constraints (do any such exist?) you will at least have all your data before hitting an error. Perhaps in this sense the PhpMyAdmin dumps are more robust?

Answer 1

It's possible to modify PHPMYADMIN to put the PRIMARY KEY and attributes directly into the CREATE TABLE structure. In this example, I'll explain how to do it using a WAMP server.

We want to edit the following file, and make two changes:

C:\Networking\wamp64\apps\phpmyadmin5.0.2\libraries\classes\Plugins \Export\ExportSql.php

1. On line 1822 I commented out:

   // $ create_query = $ statement-> build ();
   

2. On line 1720 I commented out:

   $ alter_header = 'ALTER TABLE' at line 1822 end statement}
   

Afterwards I tried export and it works with PRIMARY KEY and attributes placed in CREATE TABLE structure.