Unique index on multiple columns vs single column index

I have a table in the following structure. Now from a data integrity standpoint I want to add a joint uniqueness index on product_id and item_id. Now there can be around 100K product_id and 50k item_id. Generally my queries would like Select item_id from product_items where product_id = 'some-id' and item_type=2. Will the uniqueness index be sufficient in this case or should i add a separate index on product_id as well. Also does uniqueness order matter when creating unique index

product_items
      :id, 
      :product_id
      :item_id
      :item_type (int)
      :created_at
      :updated_at

Since your DBMS isn't specified, I'll respond in general terms.

Assuming your uniqueness index specifies product_id as the first column and item_id as the second, the index can be used either to locate a specific row based on product_id and item_id, or all rows with a given product_id.

An index can be used to speed searches when the query uses the first column defined as part of the index, and will locate rows based on the first n columns in the index if the WHERE clause (or JOIN conditions) use all of the first n rows.

So, if product_id is the first column listed in the index's creation, then it could be used any time you're locating records based on product_id. The index would not be used for SELECT product_id FROM product_items where item_id = 'some-id' and created_at > '2018-01-01', because item_id isn't the first column in the unique index.

NOTE: Your unique (non-clustered) index might well be scanned if the query was SELECT product_id FROM product_items WHERE item_id = 'some-id', because it would be a covering index. Since all the columns needed be the query exist in the index, the DB engine may be able to treat the index as if it were the product_items table itself, allowing it to just read the index, instead of the entire table and the full data rows. (If the unique index is also product_items's clustered index, then the index is the table).

StackExchange Database Administrators Q#197670, answer score: 4