Mysql – Is it better to add an index and search via the index or add a unique constraint and let an insert fail

index-tuningMySQLunique-constraint

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

Best Answer

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.

Related Question