reorganise tables after delete

[devinemke]

When I delete records from a table in mySQL, the next insert to the table reuses the previously used record position (as explained in the manual)

In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganise tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See section 4.5.1 OPTIMIZE TABLE Syntax and section 4.4.6.10 Table Optimisation.

When I run OPTIMIZE table however, it does not reorganize the records as mentioned above. The new records are still in the old positions. Doesn't the maunal mean that the records will be reshuffled to be in numerical order? Is there another way to reshuffle?(based on the primary key number) I realize that I can always ORDER BY in a query but when I am just browsing through a table in phpMyAdmin the records are not in numerical order after I delete a record.

[diego25]

To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement

It says that the OPTIMIZE TABLE statement will reclaim UNUSED space...so it will do nothing with the already-inserted rows.

What do you mean you want them in numerical order? Do you want the primary key ID to be sequential (i.e. no gaps in between the numbers)? Then it's not possible, because an old ID cannot be reused if it's an auto increment field.

Diego

[devinemke]

thank for the response Diego but i do not want (or need) the primary key ID to be sequential, i realize that there will be gaps. What i want to know is if there is any easy way to reorder the records:

lets say i have these records:
1
2
3
4
5

and i delete record "3":
1
2
4
5

when i insert a new record i have this:
1
2
6
4
5

when what i want is this:
1
2
4
5
6

I realize that i can simply write a query with ORDER BY but when browsing the table using any mySQL front-end (like phpMyAdmin) the order is whatever the order the records were inserted. Since mySQL reuses old record positions after a delete, the records will appear out of order in the db. I thought (from the manual) that OPTIMIZE would reorganise the records but it does not seem to do that. Is there another way?

[diego25]

Sorry, I don't know much about MySQL itself, so I can't help you much...but I have MySQL 3.23.46-nt and it doesn't do what you say it should do...

Diego