Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job:

mysql> DESCRIBE job_views;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| job_id             | int(11)      | YES  |     | NULL    |                |
| session_id         | varchar(255) | YES  | MUL | NULL    |                |
| created_at         | datetime     | YES  |     | NULL    |                |
| updated_at         | datetime     | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

Before adding a row to the table, I want to ensure that there isn't already a row with the same job_id and session_id. Currently the application queries the table for a row that matches (presently without an index, although an index on the two columns has been added), but I believe a unique constraint makes more sense.

Is there a difference between a SELECT using the index and then an INSERT if a row isn't found versus an INSERT that fails because of a unique constraint? If so, which is preferable (or how can I make that determination)?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Use a unique constraint (unique index), this is exactly what they're for.

The difference will be in your code. If you use the unique constraint method, you need to catch the error that the dbms will throw on duplicate entries, but if you check for the value explicitly first, you won't need to catch an error.

However, if you don't use the unique constraint method, you're not really restricting the duplication of the data at the database level, allowing the data to be corrupted easily by another application or even a maintenance script being run by a careless developer or dba.

share|improve this answer
    
I asked this question because we identified the query was a slow point so I was asking more in terms of speed, but you make a good point about data integrity. –  Daniel Vandersluis Apr 24 '13 at 17:05
    
It should perform the same way, since a unique constraint is technically an index. During the insert operation, that index would be scanned explicitly by the database for the new values, throwing an error if the values were found. –  druzin Apr 24 '13 at 17:12
    
Okay that's what I had assumed, thanks! –  Daniel Vandersluis Apr 24 '13 at 17:16
    
Np. Just approve the answer ;-) –  druzin Apr 24 '13 at 17:29
    
Of course, depending on what it means when you're trying to insert a record that's already there, you don't even have to let it "fail" -- you could use INSERT IGNORE in conjunction with the unique constraint. If the record's not there, it gets inserted, if the record is already there, nothing happens and no error occurs. –  Michael - sqlbot Apr 26 '13 at 0:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.