MySQL – Troubleshooting Super Slow Performance

innodbinsertMySQLperformancequery-performance

I have a database of movies that I downloaded, it is around 51mb in size.
One of the tables has more than 750k records(all separate insert statements(I believe this is bad)). The rest aren't so bad with the second largest table being about 10k records.

I am a super noob when it comes to mysql, but I know that taking over an hour to insert 40k records is not right.

I have looked around and people have said to increase "innodb_buffer_pool_size", I set this to 4G(don't know if that is too much), but still not much of a performance improvement.

Is there any advice that you guys can give me to speed up this process?

NOTE : This is running on my home PC as a test database(Not sure if I should give the specs or not).

Please let me know if I forgot files or code something, this is my first time on dba.

Thanks in advance!

Best Answer

  • Use LOAD DATA INFILE statement

  • If you can't then try using one INSERT statement for as much rows as you can :

    INSERT INTO table (COLUMN1, COLUMN2) 
VALUES 
    (1, "row1"), 
    (2, "row2"),
    -- Snip
    (10000, "row1000");

Con: You may have an error with your 750k row because your query is too large for the default setting. You would have to create many INSERT statements which does contains many rows values.

These two methods should already help you without any extra changes and was enough for my little datasets (100k).
I found an article about how you can speed up insert which does also talk about extra optimization for innodb. (Sorry my reputation doesn't allow me to have add extra link...)

Related Question