Title: Multiple row inserts overhead
Last modified: May 4, 2018

---

# Multiple row inserts overhead

 *  Resolved [ruperty](https://wordpress.org/support/users/ruperty/)
 * (@ruperty)
 * [8 years ago](https://wordpress.org/support/topic/multiple-row-inserts-overhead/)
 * I have a scenario where I have lots of rows (thousands) to insert (from a plugin)
   which I am doing as a single query, something like this
 *     ```
        INSERT INTO inPxUtBI_follow_data (screen_name, name, description, active, time) values (%s, %s, %s, %s, %s),(%s, %s, %s, %s, %s),(%s, %s, %s, %s, %s),(%s, %s, %s, %s, %s)
       ```
   
 * $wpdb->query($wpdb->prepare($sql, $x[‘sqldata’]))
 * However, if a different user inserts a record with a unique key then this command
   above will fail. (I presume the whole rows will fail).
 * A way around it is to do one row at a time, but would that be much slower?
 * Or is there a way of doing the multiple insert which only fails for the unique
   rows?
 * Regards,
    Rupert

Viewing 4 replies - 1 through 4 (of 4 total)

 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [8 years ago](https://wordpress.org/support/topic/multiple-row-inserts-overhead/#post-10248262)
 * If the unique key column were set to auto-increment, wouldn’t the DB engine manage
   values so that new insertions wouldn’t fail? I don’t have much experience with
   unique keys, so IDK. Or is the unique key column part of your insertion data?
 * What I’m really here to say is oh yeah, a row at a time would be much slower!
   You are going to need to chunk the data anyway, but one row is much too small
   a chunk. You will run up against execution time limits and query string length
   limits with huge data insertions, thus the need to chunk the data. I’m thinking
   several dozen rows, not one at a time. There are many variables that influence
   how much data you can insert at once.
 * You could do a verification query before insertions of only the data in that 
   chunk. Something like …WHERE key_col IN (12,13,14,15,16,…{chunk’s unique key 
   insertion data}) Anything that comes back will need to be fixed in the insertion
   data.
 *  [Dion](https://wordpress.org/support/users/diondesigns/)
 * (@diondesigns)
 * [8 years ago](https://wordpress.org/support/topic/multiple-row-inserts-overhead/#post-10248304)
 * Would using `REPLACE INTO` instead of `INSERT INTO` help your situation?
 * [https://dev.mysql.com/doc/refman/5.6/en/replace.html](https://dev.mysql.com/doc/refman/5.6/en/replace.html)
 *  Thread Starter [ruperty](https://wordpress.org/support/users/ruperty/)
 * (@ruperty)
 * [8 years ago](https://wordpress.org/support/topic/multiple-row-inserts-overhead/#post-10248470)
 * [@bcworkz](https://wordpress.org/support/users/bcworkz/) Yes, the key is part
   of the insertion data.
 * [@diondesigns](https://wordpress.org/support/users/diondesigns/) Thanks, yes,
   I think REPLACE might do it.
 *  Thread Starter [ruperty](https://wordpress.org/support/users/ruperty/)
 * (@ruperty)
 * [8 years ago](https://wordpress.org/support/topic/multiple-row-inserts-overhead/#post-10249408)
 * REPLACE works, thanks.

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘Multiple row inserts overhead’ is closed to new replies.

 * In: [Developing with WordPress](https://wordpress.org/support/forum/wp-advanced/)
 * 4 replies
 * 3 participants
 * Last reply from: [ruperty](https://wordpress.org/support/users/ruperty/)
 * Last activity: [8 years ago](https://wordpress.org/support/topic/multiple-row-inserts-overhead/#post-10249408)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics