Multiple row inserts overhead

  • Resolved ruperty

    (@ruperty)


    6 years, 7 months ago

    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

    (@bcworkz)

    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

    (@diondesigns)

    Would using REPLACE INTO instead of INSERT INTO help your situation?

    https://dev.mysql.com/doc/refman/5.6/en/replace.html

    Thread Starter ruperty

    (@ruperty)

    @bcworkz Yes, the key is part of the insertion data.

    @diondesigns Thanks, yes, I think REPLACE might do it.

    Thread Starter ruperty

    (@ruperty)

    REPLACE works, thanks.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Multiple row inserts overhead’ is closed to new replies.