0

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?

  • No tag exists for Adminer. I prefer it to PhpMyAdmin. – TRiG Nov 25 '19 at 16:31
  • 3
    CREATE+INSERT+INDEX is more fast then CREATE+INDEX+INSERT. – Akina Nov 25 '19 at 17:44
  • So that means that my subjective impression is wrong? – TRiG Nov 25 '19 at 18:55
  • Dropping indexes before doing batch inserts is a frequently used strategy to speed up bulk inserts. This is just an example of that. Why don't you do a test on your system and see how it works out for you? – Vérace Nov 26 '19 at 0:40
  • @Akina - That does not apply to the PRIMARY KEY and AUTO_INCREMENT, especially when the data to insert is in PK order. – Rick James Nov 27 '19 at 5:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.