Inserting 60 000 rows taking too long

Discussion in 'MySQL' started by Kuna, Mar 15, 2014.

0
  1. #1
    I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting.
    Shouldn't it be faster?
    INSERT INTO ip2nation (ip, country) VALUES
 (687865856, 'za'),
 (689963008, 'eg'),
 (691011584, 'za'),
 (691617792, 'zw'),
 (691621888, 'lr'),
 (691625984, 'ke'),
 (691630080, 'za'),
 (691631104, 'gh'),
 (691632128, 'ng'),
 (691633152, 'zw'),
 (691634176, 'za'),
 (691650560, 'gh'),
...
    Code (markup):

     
    Last edited: Mar 15, 2014
    Kuna, Mar 15, 2014 IP
  2. billzo

    billzo Well-Known Member

    Messages:
    961
    Likes Received:
    278
    Best Answers:
    15
    Trophy Points:
    113
    #2
    You are doing about 16 inserts a second. That is pretty fast. Are you running any anti-virus programs? If so, did you try disabling it? I had to backup some files yesterday and after I disabled anti-virus it went faster.
     
    billzo, Mar 17, 2014 IP
  3. Kuna

    Kuna Well-Known Member

    Messages:
    426
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    105
    #3
    I will try, thanks
     
    Kuna, Mar 26, 2014 IP
  4. ryan_uk

    ryan_uk Illustrious Member

    Messages:
    3,983
    Likes Received:
    1,022
    Best Answers:
    33
    Trophy Points:
    465
    #4
    You might be better of providing the DB structure, including the type of storage engine you are using. There might be ways to improve this and speed up the inserts.
     
    ryan_uk, Mar 26, 2014 IP
  5. pentaxial

    pentaxial Active Member

    Messages:
    101
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    78
    #5
    Hi, if you doing by Manual, its based on Internet connection and server you are using.

    if you using Dedicated server, you can use mysqldump import -> it may take only 1 min so

    Thanks
     
    pentaxial, Apr 7, 2014 IP
  6. pmf123

    pmf123 Notable Member

    Messages:
    1,449
    Likes Received:
    81
    Best Answers:
    0
    Trophy Points:
    215
    #6
    it also depends what indexes you have setup on the database itself, and how many existing records, as this slows down the INSERT a lot.

    I tried inserting 130 million records in 4 separate batches the other day into an empty table and only the first file was quick, probably because of the indexing i was doing.

    So I combined the 4 files into 1 and then did the insert and it finished in less than 2 hours.
     
    pmf123, Aug 31, 2014 IP
  7. immu

    immu Active Member

    Messages:
    69
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #7
    when your inserting data in tables,
    many things run behind the scenes, I should know the table structure and what kind of indexes are applied on it.
    The Indexes are one of the main causes of performance. If used well they are lightning fast if not they spoil the performance.
    btw, hardware (or resources) play a vital role.
     
    immu, Sep 4, 2014 IP
  8. Krellen

    Krellen Greenhorn

    Messages:
    38
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    18
    #8
    Did it finish yet? ;)

    It really all depends on your system, disk IO, etc. 60,000 2 column rows shouldn't take too long.
     
    Krellen, Sep 19, 2014 IP