1

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!

  • I have three questions: 1) What version of MySQL are you running ? 2) What operating system are you using ? 3) How much RAM on the DB Server ? – RolandoMySQLDBA Jun 28 '14 at 19:08
0
  • 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...)

  • thanks, figured this out just after I asked the question but haven't had a chance to give an answer. Will mark this as correct as this was the solution! Once gain thanks, this contains everything that is needed to fix the issue. – Maccle415 Jun 29 '14 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.