Here is what I have :
table content : cat_id product_id data1 data2 etc.
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
A database without Primary key is only half dressed and according to you product_id is an ideal candidate for a primary key, so let choose that. The primary key will be used in
SELECT * WHERE product_id = :prodId
It does not matter if
and cat_id = :cat_id becomes a part of the query or not unless you have thousands of
cat_ids associated with each
Then choose an index on cat_id. This will be used on
SELECT * WHERE cat_id = :cat
This will be very quick if the cardinality of the data is good. That means there is a wide distribution of
cat_ids in the table. The index of
cat_id will not be used in the first query. Thus you have two different indexes and both queries can be expected to be really quick.
[cat_id+product_id] != [product_id+cat_id] when it comes to indexing, if you have only one composite index one or the other will be slow.
For example, suppose we had a composite index on (cat_id, product_id) now the following query cannot make use of this index.
SELECT * FROM tablename WHERE product_id = :prodId
But both these queries can use the (cat_id, product_id) index
SELECT * FROM tablename WHERE cat_id = :cat_id and product_id = :prodId SELECT * FROM tablename WHERE cat_id = :catId
So in summary. Choose 1 and 2. But if the number of
cat_ids are small or there are lots of cat_ids associated with each
product_id choose 4 but make sure that the primary key is also in place.