Hello all... I'm going to propose an algorithm to compact a MySQL database containing information that I'll talk about shortly. I'm curious what you all think about it. Nevermind the various reasons why I should/should not do this, as I have weighed them in my head and decided that it's something I want to do. So, anyway, on with the show. --- My database is used by a radio station to keep track of their music assets and playlists. It contains the following tables: -albums -- Contains album data and references to other tables -artists -- Contains name and ID -genres -- Contains name and ID -labels -- Contains name and ID -media -- Contains name and ID -names -- Contains only one row... info about the radio station. -playlist -- contains a timestamp and references to users and tracks -tracks -- contains track info and references to albums and artists -users -- contains user information The names table is there so that I can easily pull the data from somewhere, but just as easily alter it from the interface... I didn't want to deal with using a file, though it wouldn't be hard... I may change that later. Anyway, because of repeated add's, delete's, etc on the name/ID tables, they are becoming fragmented. I have set the datatypes on the ID fields large enough to handle anything that they throw at it for now, but over the course of 5 years, they may begin to reach their capacity, and I will no longer be around to support it (it's a college radio station). Therefore, I have decided that I need an algorithm to compact the auto_increment fields. Here's what I'm thinking. On a table-by-table basis, create a temporary table that contains the old ID and the new ID. Then, once that table is populated, convert references in other tables from the old to new. Like this (in PHP pseudocode) result = SELECT * FROM labels; delete from labels; create temporary table labeltemp( oldid, newid ); loop through result insert into labeltemp (oldid) value (result[id]) update sometable set id=newid where id=oldid; So, that was brief and messy... but I think it will work. I'm hesitant to try it, because I can't create a new database, and I don't want to try it on live data. So, can anyone see a problem with this, aside from the old "why do you want to do that?" crap? Thanks, Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]