search

jim256 / ebay

0 stars 0 forks source link

mysql - Verify there is a unique constraint on (source, source_id) #6

Closed james-carpenter closed 4 years ago

james-carpenter commented 4 years ago

A unique constraint on those fields (source, source_id) is required to allow for atomic upserts under concurrent execution. The unique constraint will cause the insert to fail in a way that lets the update occur in the same statement.

I added this to my local to verify the desired behavior. alter table cars_ebay_test add constraint source__source_id_uniq unique (source, source_id);

jim256 commented 4 years ago

I've got the following in my table DDL:

ALTER TABLE autosearch.cars 
  ADD INDEX cars_source_source_id_IDX(source, source_id);

How does that sound? I found that to help a lot for the lookup speed. I have about 7,000,000 rows in my production table.

james-carpenter commented 4 years ago

@jim256 That's great for the index and speed as you mentioned.

A unique constraint on those fields essentially defines the natural key for the table. Because of the way you describe those being used, I assume you do not have any duplicate rows with the same (source, source_id).

As long as there are currently no duplicates, adding this constraint would prevent that issue in the future and let you take advantage of the automatic insert/update capability of MySQL.

jim256 commented 4 years ago

@james-carpenter got it.

If I change that index to a unique index, does that do the exact same thing? Or is it a little different?

james-carpenter commented 4 years ago

Unique index is perfectly fine and will accomplish the same goal. Underneath, the unique constraint would have created the index anyway.

Either will force uniqueness on that combination of fields. Thanks.