MySQL Forums
Forum List  »  InnoDB

InnoDB Clustered Indexes
Posted by: Marc Bouffard
Date: April 18, 2005 10:44AM

I am trying to figure out a way to delete certain records from a table, but in such a way as to never need an OPTIMIZE TABLE.

Right now, we have some records that need to be kept around until the event they represent is completed. Sometimes this can take a very long time. If we delete records from the beginning of the table, and none of these were still in an "open" state, then no problem. If some of these records were in an "open" state, then the delete should cause some gaps and fragmentation which can be fixed with an OPTIMIZE TABLE.

Since the data of the record is physically located with the primary key in a clustered index, I was thinking we could assign "open" records a very high value for the primary index, which will guarantee that when we delete records from the beginning of the table, we do not leave any gaps.

Then when the record becomes "closed", we reduce the size of the primary key, which will cause it to rewrite the record at the beginning of the table. I realize this is an expensive procedure as it will need to move the data, change the key, and all secondary indexes. The question is: will it work the way I think it will and allow deletes on this table to leave no gaps?

Also, is there any tool out there that will allow you to look into an ibdata file and see how it writes data to disk? Any tool that may expose the indexing/data of an ibdata file so you can see exactly how it is written to disk?

Thanks in advance,


Options: ReplyQuote

Written By
InnoDB Clustered Indexes
April 18, 2005 10:44AM
April 24, 2005 02:27PM

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.