Composite key or New Unique key

I want to know which will be the best. I am having a table say A having col1(is a foreign key fk1),col2(is also a foreign keyfk2). Can I use PK as (col1,col2) or a newly generated PK. The queries will have fk1 in criteria and f开发者_C百科k2 in joins. Kindly suggest me a best one.

P.S I am using mysql

---

If this is a many-to-many link table, just use (col1, col2) as a PK.

Foreign keys in MySQL imply InnoDB which is index-organized, this means that the table itself will be the PRIMARY KEY and it's records will be ordered.

If you are also going to search for col2 and use col1 in joins, you'll need to create an additional secondary index on (col2). This index will implicitly include the PRIMARY KEY values as the record pointers, and, hence, will in fact be and index on (col2, col1, col2).

---

As others have said - if this is a linking table, use col1,col2 as PK. If on the other hand this table can also be linked TO, then create a new key of its own. You can also put a unique constraint on the col1,col2 pair.