[RESOLVED] OPTIMIZE TABLE - the auto-increment col ?

steamPunk

I need to delete about 90% of the old orders from the db and this will free up a lot of space but, from what I've read, I will have to do an OPTIMIZE TABLE to defragment the tables.

I have an order header table 'comm_en' that has an auto-increment col for the order number and an order items table 'comm_li' where each line belonging to an order is refernced using the order number from the 'comm_en' table.

If I do OPTIMIZE TABLE will this have any effect on the auto-increment values in 'comm_en' ? Is there any risk of the auto-increment being reset ?

I had a look in the manual but it's not too clear https://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

thanks

Weedpacket

steamPunk wrote:
If I do OPTIMIZE TABLE will this have any effect on the auto-increment values in 'comm_en' ? Is there any risk of the auto-increment being reset ?

It would be stupid if it did. Then again, it probably shouldn't matter.

I had a look in the manual but it's not too clear https://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

The first sentence oon that page starts "OPTIMIZE TABLE should be used if you have deleted a large part of a table..." Seems pretty clear to me.

steamPunk

thanks for your reply

I was talking about the line "Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions" - I wasn't sure if it meant that the auto-increment value would be changed - but, no matter, it sounds like it should be ok.

Derokorian

The existing / remaining AI values will be unaffected. What it does mean is that the AI values that you deleted will be reused. So if you have 1-10 and you delete 2, 5, 7, and 8 when you insert another row after optimizing, instead of the new row having an AI value of 11 it will instead have 2, and the next will be 5, and so on until the missing values have been filled in.

bradgrafelman

@: No, the auto increment number is completely unaffected. MySQL will not re-use old IDs after an OPTIMIZE operation.

EDIT: At least, that's the behavior on my 5.1 production server, and the MySQL manual certainly doesn't suggest otherwise.

Derokorian

Ah, ok. Don't have access to test it out while at work. I made an inference based on the following quote:

... subsequent INSERT operations reuse old row positions

bradgrafelman

Since that was in referenced to a "linked list", I'm assuming the "position" was more of a physical/logical position in the raw datastore that MySQL maintains on disk/in memory. As far as the actual DB engine is concerned, an AUTO_INCREMENT column is no more related to a row's "position" than any other column is (which is why, in the absence of an 'ORDER BY' statement, a result set should not be assumed to be in any predictable/deterministic order).

johanafm

steamPunk;11014787 wrote:
Is there any risk of the auto-increment being reset ?

Why are you even asking this question? If you're worried that the AI would collied with rows that weren't deleted, you don't have to. Optimize table is recommended to be used after lots of rows have been deleted, thus it's safe to use with the remaining rows, wether the would reuse deleted values or not.

If you're depending on the AI being in some particular order, you are doing something very wrong. The only thing you should expect to get from the AI is a unique integer for each and every row. They did indeed implement it so that it keeps giving increasing values, but that's due to simplicity of implementation along with the fact that the actual values doesn't matter at all. Had it been easier to implement unique values that were completely random, they would be.

But, as far as the question in this thread goes, brad's correct as far as I know.

Ashley_Sheridan

I think deleted AI values only get used if the AI is reset, or the AI reaches the upper limit for the assigned data type, i.e. if you had the AI field set up as a TINYINT, and you reached a row with an AI value of 255, it would then look to use next available value, which would be the lowest deleted row AI value.

bradgrafelman

Ashley Sheridan;11014869 wrote:
it would then look to use next available value, which would be the lowest deleted row AI value.

No, it wouldn't; it would fail to INSERT any new rows and generate error #1062 until you manually adjusted the AUTO_INCREMENT counter and/or updated the column type to allow it to grow again.

Ashley_Sheridan

Ah right, I've learnt something today then! Not ever really come across it before, I've always set the AI field to use a sensible type, but good to know for the future!