#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Location
    The Netherlands
    Posts
    12
    Rep Power
    0

    Inserting 86,000 rows at the same time?


    Hello,

    For something I am working on I have to insert 86,000 rows in the same table in a database. I'll be using a for-loop in PHP for this.

    Is a database able to handle so many insert-queries at the same time, and if not, is there any other effective way to do so?
  2. #2
  3. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,102
    Rep Power
    1990
    Code:
    INSERT INTO table (id, name) VALUES (1, 'First Item'), (2, 'Second Item')
    And keep adding all of the extra values to that string. It won't work instantly with that many records, and you might be limited to the available memory in PHP, but the MySQL server can handle many records at the same time this way.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Location
    The Netherlands
    Posts
    12
    Rep Power
    0
    Originally Posted by Catacaustic
    Code:
    INSERT INTO table (id, name) VALUES (1, 'First Item'), (2, 'Second Item')
    And keep adding all of the extra values to that string. It won't work instantly with that many records, and you might be limited to the available memory in PHP, but the MySQL server can handle many records at the same time this way.
    I know the query itself, but the point is that every record has some random generated information. My idea was to create a for-loop and do a query at every loop.

    Would that work?
  6. #4
  7. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,102
    Rep Power
    1990
    Yes, it will work that way. It just performs 86,000 separate queries.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Location
    The Netherlands
    Posts
    12
    Rep Power
    0
    Originally Posted by Catacaustic
    Yes, it will work that way. It just performs 86,000 separate queries.
    Oh, good. That's what I wanted. It will only be used once so if it would be slow that's not a big problem.

    Thank you for helping me out.
  10. #6
  11. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Unless there is very large data in each insert you should quite easily reach about 3000 inserts per second (that's what I get on my laptop and it is about 5 years old by now).

    But if you are running InnoDB and don't have a RAID controller with write back cache you can experience slow performance (around 100 queries per second) with the default setting.
    The trick is to set innodb_flush_log_at_trx_commit = 2 in the Mysql configuration.
    The value two means flush to disk about once per second, which speeds up small inserts a lot, but the disadvantage is that if the OS dies you might loose that 1 second of queries which is why it isn't set as default.

    Otherwise there is no limit of how many queries you can run against a database, the database will automatically queue up queries and slow down the party that tries to perform them all so you get like a normal throttling in case you start get near the max performance.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo