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]
