Unless I completely misunderstand your question, I don't see how the "id" field would ever be of use. You said you aren't going to be selecting on "id", only by one or the other of association_id1 or association_id2. If you are really worried about the importance of inserts / updates / deletes, and not about retrievals, then don't index anything. A SELECT will have to serially access the table, while a write will have to shuffle the indices.
Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Chris White [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 03, 2006 2:20 PM > To: mysql@lists.mysql.com > Subject: Combined Primary Key and Auto Increment Primary Key > > Hi all, > > I had somewhat of a performance question. I have an > association table with 2 > unique values which will always be selected by one of the > values (never by > id). That said, I'm wondering which would be a better gain, > having this: > > CREATE TABLE association_sample ( > `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, > association_id1 INTEGER NOT NULL, > association_id2 INTEGER NOT NULL > )ENGINE=InnoDb; > > or > > CREATE TABLE association_sample ( > association_id1 INTEGER NOT NULL, > association_id2 INTEGER NOT NULL, > PRIMARY KEY(association_id1,association_id2) > )ENGINE=InnoDb; > > note that this table will mostly consist of table writes > (updates, inserts, > deletes). > -- > Chris White > PHP Programmer > Interfuel > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
