I think you may need to use a scripted approach in order to avoid
overflowing your disk.
1. Create a new empty table with the correct structure.
2. Copy some "block" of records (like those with id values between 1 and
1000) into the new table
3. Delete the same records (the ones you just duplicated) from your
existing table
4. Repeat from 2. but for the next block of records (1001 to 2000, 2001 to
3000, etc) until all records have been migrated.
Depending on the amount of free space on your disk and the size of each row
of data, you may need to adjust the number of rows you move in a "block".
If you had the room I would say dump the table to a text file then
re-import them into the new table but I don't think that's possible in your
situation as you already said you didn't have enough space for a temp
table.....
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
aman
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
es.com> cc:
Fax to:
07/08/2004 04:48 Subject: re structuring tables for a
huge database
PM
Hello All
I have a table (about 72 GB of data).
When the table was created, not much attention was paid to the design
aspect.
For ex.,
id int(10) should only by tinyint(3)
name char(35) should just be char(20)
and many more like that.
I do not have enough space on the hard drive to create a temp table.
I have to re structure the table and I am seeking some suggestions.
I plan to use
ALTER table <tablename> MODIFY <COLUMNNAME>....
So I would be doing
"Alter table host modify id tinyint(3) not null primary key
auto_increment"
Are there any Alerts/Comments/Suggestions for the approach?
I have to modify almost all the pre-existing columns - is there a
quicker way?
Regards
Aman Raheja
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
