MySQL Forums
Forum List  »  Performance

Re: MySQL InnoDB Performance vs Postgres
Posted by: Jay Pipes
Date: July 21, 2005 05:37AM

The issue is that, as Peter suspected, you have no PRIMARY KEY defined on the table, and, in this case, InnoDB assigns a 6-byte automatic clustering key to each record. The secondary indexes (built on top of the clustering key) on the two KEY columns (userid and friendid) will have this additional 6-bytes prepended to each record. The proper way to define this table is to remove the id column entirely, as it serves no purpose, and make the PRIMARY KEY on (userid, friendid).

However, this brings up an additional normalization problem in that the table (I think) is supposed to store friend records, yet there is no clear identifier for the friend record (is it id or friendid). This makes me think that this is in fact a many-to-many relationship between a user and a friend. If this is the case, then you should have a separate relating table which simply stores the userid/friendid key, and separate off the friend entity attributes into it's own table.

Additionally, if you aren't enforcing foreign key constraints, why are you using the InnoDB engine?

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL InnoDB Performance vs Postgres
2089
July 21, 2005 05:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.