MySQL Forums
Forum List  »  MyISAM

Posted by: Steve Criddle
Date: July 25, 2008 08:15AM

Short version of the question: Is there any performance difference between a PRIMARY and UNIQUE index on a MyISAM table?

Long version of the question:

We currently have a table which has a PRIMARY key on a BIGINT column. This column is unique (obviously) but doesn't run sequentially (it's not auto-incrementing or anything). The table itself is basically used for something a bit like logging. Rows are inserted and can then subsequently be updated. The data is archived regularly to another database system and rows are deleted automatically once they are 14 days old. So the table is always on the move and no data lasts more than 14 days in it because of the archiving.

We now have a requirement for an auto-incrementing value in this table. But because it already has a PRIMARY key, the only way to achieve this using AUTO-INCREMENT is to change the current PRIMARY key to a UNIQUE index first.

Can anybody tell me if this will have any performance impact? Will it make database accesses on the table any slower? I know that some database systems use the primary key to dictate where the data is stored (to speed up retrieval), but I do not know if this applies to MySQL with MyISAM.

Options: ReplyQuote

Written By
July 25, 2008 08:15AM
July 25, 2008 08:36AM

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.