Composite key VS primary key + not unique index
<p>Here is what I have :</p>

<p><code>table content :  cat_id  product_id  data1  data2  etc.</code><br>
the categories are not unique obviously.
the product ids are unique.</p>

<p><code>2 queries : 
1 -- SELECT * WHERE cat_id = :cat - must be as quick as possible
2 -- SELECT * WHERE product_id = :prodId
In second select, I can add : AND cat_id = :cat</code></p>

<p>What is the more efficient ?</p>

<ul>
<li>1 - index <em>(not unique)</em> on cat_id (good for select 1)</li>
<li>2 - primary key on product_id (unique -> excellent for select 2)</li>
<li>3 - index <em>(not unique)</em> on cat_id + PK on product_id (good for 1 &amp; 2 separately)</li>
<li>4 - unique constraint with composite [cat_id+product_id] (good for 1 &amp; 2 together)</li>
<li>5 - same as 4, but defining the composite as PK</li>
<li>6  - composite (4 or 5) + single index/PK</li>
</ul>

<p>For information, I'll have around 20 products in each category and a lot of categories (say 3000) - And (as it is unique in table) ONE product belongs to only ONE category - In fact, that is not really cats and products, that is for the simplicity of explaination;)</p>

<p>thanks!</p>