Importing huge XML file to MySQL

Question

I'm in the process of downloading a 900mb XML file that a client wants me to upload into a single table in a MySQL database. I had to insert a 6mb file a week or two ago which I did my converting the file to a SQL query which I could run in phpmyadmin, but that took a fairly long time.

How should I go about it this time? Thanks

Answer 1

I would see if the file could be converted to CSV format. It will be much quicker to import into a MySQL table as there is no processing of the XML. You just have to be careful with the data types being input for each column.

If the client can't give you it in CSV, and this is a one time upload, try converting it yourself using MS Excel/Libre Calc/OpenOffice Calc.

Answer 2

If it will be a weekly occurrence and the XML and database structure will remain the same use a sax parser (as opposed to using Dom) to generate SQL file and import via a mysql command line.

mysql -u user -p database name < import.SQL

You can compress the SQL file before uploading to the server to get it up quicker

You may consider locking tables as required to further improve performance (if the system can handle it) http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html