2

I'm sorry if this question is stupid or already asked, but I couldn't find much about it.

What is fastest / best method of unique storing in SQL

Option 1: Create unique index, and use a try -> catch block with PHP? Option 2: Query to check if exists, and then act on that?

I would think option 2 is the best, but with option 1 I only have 1 query, vs 2 queries if not exists.

And since I need to minimize DB queries the best I can, I would go for option 1, but not sure if it a good option to bypass it with the try block?

Thanks

Convertor
  • 143
  • 1
  • 1
  • 12
  • 2
    Creating `Unique Index` and then using `INSERT IGNORE`, might be the best way. – Criesto Oct 15 '15 at 11:43
  • Yes, thats an good point, I had forgot about the IGNORE statement, but then is IGNORE good to use? – Convertor Oct 15 '15 at 11:46
  • Yes, if you want to remove the `IGNORE` there are lot of other options like using mysql's `REPLACE` or `INSERT (...) ON DUPLICATE KEY UPDATE...`, these will be a bit slower as both `INSERT` or `UPDATE` will take place. – Criesto Oct 15 '15 at 11:55

3 Answers3

1

As with all optimisation related question, the anser is: well, it depends.

But let's get one thing straight: if you do not want duplicate value in a field or combination of fields, then use primary key or unique index constraints just to make sure that the integrity of data is not compromised under any circumstances.

So, your question is really: shall I check before inserting a record in a table if the data would violate uniqueness constraints.

  1. If you do not really care whether the insert is successful or not, then do not check. Moreover, use insert ignore, so you do not even get an error message if the insert violates uniqueness constraints. Such situation could be, if you want to log if a user logs in within a certain period at least once or not.

  2. Consider how costly it is to check before each and every insert and update, if the data violates any constraint and how often do you think it would occur. If it is a costly operation, then rely on the indexes to prevent the inserts with duplicate data and find out what data violates the constraints after you know that the query has failed.

Do not only consider the cost of the select, but also take into account if the insert is part of a larger transaction which may have to be rolled back in case an insert fails. Checking before the transaction even starts for constraint violations may have a positive impact on your db's performance.

Shadow
  • 30,859
  • 10
  • 44
  • 56
  • What about inserting multiply arrays? like: https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query. With the IGNORE statement added, if it fails, will it be the whole inserting array, or only the item being inserting? – Convertor Oct 15 '15 at 12:20
  • 1
    http://stackoverflow.com/questions/1282025/bulk-insert-mysql-can-i-use-ignore-clause-is-there-a-limit-to-no-of-records – Shadow Oct 15 '15 at 12:55
0

In my opinion Always use unique property for the field you want to be, actually - unique ! If by any means you can not do so, and also want to know before hand if the desired value already exists in the table / collection then additionally use if-Exists functionality.

Why not any one? May be because improper sharding key in mongodb allows non-unique value in each shards! Cannot offer knowledge in SQL but I think methodology goes same - use unique indexing where possible.

Cost effectiveness Both methods cost server resources and hits the db server minimum twice.

So what's the big deal? In the unknown universe -- you sent a request to know if a value exists and response was false to let you know the uniqueness! By the time you recognize it in the application server and requests the insertion may be someone else has already inserted the same value, by any chance in a busy server!

The server may never bother telling you the discrepancy as the indexing was unavailable!

For this point of view if - uniqueness is that important then you should enable indexing in table or collection level first!

Saikat Dey
  • 187
  • 1
  • 2
  • 12
-1

Create unique auto incremented primary key index. and then insert the data to SQL without entering the auto incremented primary key index value.

The inserting will never duplicate the data.

A Barki
  • 1
  • 1
  • This implies in every insertion. But situation arises when you have to check for pre-existence of a value. And then you need to verify it by calling a checking method or read a server response and so. – Saikat Dey Jul 31 '20 at 18:10