...

View Full Version : Is this bad methodology?



Keleth
05-05-2011, 11:16 PM
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?

Old Pedant
05-05-2011, 11:23 PM
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.,


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.

Keleth
05-06-2011, 04:13 AM
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!

Old Pedant
05-06-2011, 04:23 AM
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.

Keleth
05-06-2011, 06:35 AM
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).

Old Pedant
05-06-2011, 09:09 PM
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.

Keleth
05-07-2011, 03:34 PM
Yah, MySQL... and I did not know about INSERT IGNORE... back to the documentation!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum