Mysql – PHPMyAdmin very slow inserts

bulk-insertinsertMySQLperformancephpmyadmin

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.

Best Answer

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.

Related Solutions

Sql-server – the most efficient way to get the minimum of multiple columns on SQL Server 2005

I tested the performance of all 3 methods, and here's what I found:

1 record: No noticeable difference
10 records: No noticeable difference
1,000 records: No noticeable difference
10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one.
100,000 records: UNION subquery is significantly slower, but UNPIVOT query becomes a little faster than the CASE WHEN query
500,000 records: UNION subquery still significantly slower, but UNPIVOT becomes much faster than the CASE WHEN query

So the end results seems to be

With smaller record sets there doesn't seem to be enough of a difference to matter. Use whatever is easiest to read and maintain.
Once you start getting into larger record sets, the UNION ALL subquery begins to perform poorly compared to the other two methods.
The CASE statement performs the best up until a certain point (in my case, around 100k rows), and which point the UNPIVOT query becomes the best-performing query

The actual number at which one query becomes better than another will probably change as a result of your hardware, database schema, data, and current server load, so be sure to test with your own system if you're concerned about performance.

I also ran some tests using Mikael's answer; however, it was slower than all 3 of the other methods tried here for most recordset sizes. The only exception was it did better than a the UNION ALL query for very large recordset sizes. I like the fact it shows the column name in addition to the smallest value though.

I'm not a dba, so I may not have optimized my tests and missed something. I was testing with the actual live data, so that may have affected the results. I tried to account for that by running each query a few different times, but you never know. I would definitely be interested if someone wrote up a clean test of this and shared their results.

MySQL slow queries – Fast using PHPMyAdmin , low CPU and RAM usage

You need to identify which queries are taking some time to execute and optimize accordingly. By default mysql does not log slow queries, so you have to add the following to your mysql config file -

log-slow-queries
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10     // in seconds - you can replace this with another number.

For any mysql performance issues, it is a good idea to use mysqltuner

Best Answer

Related Solutions

Sql-server – the most efficient way to get the minimum of multiple columns on SQL Server 2005

MySQL slow queries – Fast using PHPMyAdmin , low CPU and RAM usage

Related Question