Basic question regarding optimize table
Posted by: marc johnson
Date: July 20, 2011 11:24AM


We are using MySQL community edition version 5.0.77 included with RHEL 5.5.

I hope this post/question/train wreck will be interesting to at least some of you out there and there is a solution (viable) for what we have encountered.

I'm trying to deal with an issue caused by poor design and compounded by no real maintenance.

I have one table that has many columns written to it each day and many of those columns are then subsequently deleted. The primary key, the `id` column is of data type int and is set to auto_increment.

The issue is that we have hit the max value for int @ 2,147,483,647 in the `id` column however there are currently only 206,480,145 entries in the table. Obviously, there are many gaps in the id field.

I'm relatively new to MySQL so I'm not exactly certain regarding the precise terminology but ideally what I would like to do (or what I think I would like to do) would be to remove the gaps in the index between successive rows with the final result being that the last row in the table would have a value = the total number of rows currently in the table.

I have read all the documentation regarding check, defragment, and optimize table. After reading it it seemed to me that optimize table would accomplish my goal.

I ran optimize table as:

mysql> optimize table my-broken-table;
| Table | Op | Msg_type |Msg_text |
| | optimize | status | OK |
1 row in set (47 min 30.74 sec)

So I thought great, that should do it. But then I view the table and basically it appears that absolutely nothing has changed.

Do I have a fundamental misunderstanding of what this function provides or might there be something else going on?

Any help, recommendations are welcome.



Options: ReplyQuote

Written By
Basic question regarding optimize table
July 20, 2011 11:24AM

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.