purging unused keys from a MyIsam table.....

[dmmh]

somewhere, somehow, my host decided to remove INNODB tables from the list of allowed table types and this has given me some problems. In order to reclaim unused keys I used to do a simple 'ALTER TABLE $table TYPE=INNODB'

and I would be able to re-use purged keys. Somehow, this wont work. Well, the query succeeds apperently, but the deleted keys still exist and the next autoindex is way to high.
A OPTIME query doesnt work either. #*$! am I doing wrong, or what can I do to fix this?

I dont want any URLS that point to nothing.......

[jatar_k]

I think you can do something like

alter table table_name auto_increment = x;

the user comments here
[dev.mysql.com...]
seem to support this

The only way I can think of to clean up all the unused keys, if there are a ton, is to use a script to roll them all down so they are sequential and then use the above command to reset the top end

[dmmh]

dammit, that sucks. thanks for the help though

[coopster]

Here is a related thread that shows how to reset the auto_increment columns.

[webmasterworld.com...]

Don't forget, always make a backup before you test something like this!

[dmmh]

IMO this is one very important, not very well documented 'feature' and advantage of INNO DB storage types: its easy to purge empty keys

I had my host look into the problem and it appears the server admin (my server is in the US whereas my host is Dutch) had altered the local MySQL configuration, leading to missing INNO DB support. Now I can easily reclaim unused keys by doing 'ALTER $table TYPE=INNODB'

I think this is usefull information to a lot of people. INNO DB may be a tad slower, but in my opinion this doesnt outweigh the importance of data security/ efficiency and this is where INNO DB excells

hope it helps other people

Regards,

DmmH