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)?


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.

  • 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. 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
  • 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. Apr 26 '13 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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