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.