I am working on existing DB and try to optimize it. I see a table without a single primary key but with two foreign keys work as a primary key. I know it will work. however, is it better to have one single primary key with two foreign keys for better performance or primary (key1, key2) will just work as good as one?
For example:
CREATE TABLE ABC (
'xid' int(11),
'yid' int (11),
PRIMAY KEY (xid, yid)
)
does it perform the same (in terms of indexing) as:
CREATE TABLE ABC (
'id' int(11),
'yid' int (11),
'xid', int (11),
PRIMARY KEY (id),
KEY (xid, yid)
)
some updates there
so, I do some testing.. using simple queries on three different tables
Table myA, with 10,000+ records. only has userid as the primary index.
SELECT * FROM myA where userid=12345
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE myA const PRIMARY PRIMARY 4 const 1
Table myB, is a many-to-many table, with primary id, and userid as one of two foreign keys. with over 50,000 records
SELECT * FROM myB where userid=12345
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE myB ref userid userid 4 const 53
Table myC, is also a many-to-many table, but with composite primary key, userid is one of the two. with over 100,000 records
SELECT * FROM myC where userid=12345
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE myC ALL NULL NULL NULL NULL 101289 Using where
So, table C actually examine all 100,000 records!!! (the query only returns 50 records)
There are something that i don't understand... it seems to me composite primary isn't doing the job.
MORE..
While I'm doing more testing and "Explain", I conclude that (in MySQL), even you set composite keys as primary key. You still have to explicitly set index for all keys. Then you will enjoy indexing.