Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts

    Is this bad methodology?

    So I've got a system where I'll receive a set of names (with unique ID numbers), and I've got to make sure they're in the system.

    Would it be bad methodology to always attempt to insert the data, and relying on the fact that the id column being primary would keep it from inserting duplicate data, or should I try to select the data, find out whats not in the system, and insert only that?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,191
    Thanks
    80
    Thanked 4,562 Times in 4,526 Posts
    How will you determine *WHICH* of your set of names/ids were rejected if you go the attempted-insert route?

    If you don't care, then yes, you could do that.

    But if you want to *KNOW* which of them are there, it's going to be a lot faster to find all of them at once.

    E.g.,
    Code:
    SELECT id, name FROM people WHERE id IN ( 3718, 18267, 1192, 99182 )
    And now you find out about all those that are there already with one query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Ah, yah, I'm currently using the IN to select the relevant rows, figure out which ones are missing, the inserting the others. However, I don't care which ones aren't inserted, just that all of them end up inserted one way or another.

    From what you said, it seems acceptable to then simply insert and rely on the primary key disallowing multiples in my situation. Thanks as always!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,191
    Thanks
    80
    Thanked 4,562 Times in 4,526 Posts
    Just that it might be slightly slower.

    After all, if you have 100 id/name pairs to insert and only 3 of them are *NOT* already in the table, then you will be making 97 insert calls that do nothing.

    Whereas if you first do the SELECT you will then make only 3 inserts, for a total of 4 database operations.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Well, at most I will be inserting 3 rows in any operation (each data set is at most 3, or at little as 0).

    However, a problem I have encountered... I guess this is how it should be working, and may be because I did something wrong, and may mean I have to go with the multiple queries, but... when I tried testing an insert that had 3 rows (ie, one query, VALUES (something, something), (something, something), (something, something)), of which 1 was already in the DB, the whole query failed since there was already a duplicate primary key. Does this mean I should be doing a separate insert query for each? I take this is how its supposed to work? I figure if I have to do a separate query for each, I might as well test to see if it exists and not insert at all if its already in (one extra query, but over all faster).

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,191
    Thanks
    80
    Thanked 4,562 Times in 4,526 Posts
    This *IS* MySQL, right?

    Did you use INSERT IGNORE???

    If you don't use that, then yes, the failure of one is the failure of all.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Yah, MySQL... and I did not know about INSERT IGNORE... back to the documentation!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •