Nick,

That index would have a very poor selectivity so it probably would not
be used even if you declared it. You mention it is the primary key for
another table. If it is declared as a foreign key constraint, there
will already exist an index for it.

You could make a combined index on (Cat_ID,Product_ID), as this will
have very good selectivity and will probably be a lot faster.

Adam