PHPMyAdmin very slow inserts

Question

I was trying to insert around 70K records to mysql database through phpmyadmin and was shocked of how slow it is. It took around 5 hours to complete!

Please find below the details of the table/database:

• I only have access to PHPMyAdmin and FTP. So I cannot use SSH to upload the data unfortunately.
• I was only uploading to one table with 8 columns. No foreign keys or computed columns of any kind. Only one auto-increment for the primary key which is not part of the query
• I checked the status tab and no locks or running queries on that table to cause slowliness.
• Some of the values are in Arabic (UTF8)

I tried the following methods to upload the data:

• Separate Insert Queries through SQL tab: Extremely slow and takes 5 hours. It gave 500 error but it continued to upload the data and didn't stop.
• Joined insert queries through SQL tab [e.g. (record1),(record2),(record3)]: takes long time and suprisingly does not add anything to the database. No errors as well.
• Import Tab: Execution time errors
• Through PHP: Memory was exceeded and do not have access to increase the memory as the server is not under my control

The insert query is simple

INSERT INTO mytable (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`,`col7`,`col8`)values(1,100,10,'val1','val2','val3','val4','val5');

I would like to upload the data as fast as possible as I only have 5 minutes to provide that data to the customers. Any way to do a bulk insert to be as fast as possible. Alternatives to phpmyadmin that can be added through FTP are welcome as well.

Answer 1

The table is probably ENGINE=InnoDB. The default is probably to commit a transaction after every INSERT. You seem to be inserting one row at a time. Is the input file a list of INSERTs? Where did it come from? If it came from mysqldump, re run the dump with the option to do multi-row inserts. If the data is just text, arrange to use LOAD DATA.

Can you connect to mysql from another server? That way you won't be limited to phpmyadmin. Etc.

Check the Arabic. Non-English characters cause trouble for a lot of people. If they are mangled, you may need to reload after changing something.