Re: Innodb vs. myisam storage usage *shocked*
James,
only the tables created with
ROW_FORMAT=COMPRESSED
will be zip-compressed. Of course, if the table already contains BLOBs in compressed formats, then it is no good to advise the database to try even more compression.
There are downsides to zip-compression: it may eat up to one CPU to compress pages, and if there is corruption, the whole page will be lost, not just individual rows in it.
The compression will be completely transparent: the user can update the table like any other table.
---
Looking at the original question that started this thread: the size of the table in the MyISAM format was 20 MB, and the size in the InnoDB format was 60 MB. The space usage with the 4.x InnoDB table format can indeed easily be 3 x the MyISAM size. The new InnoDB format in 5.0 may in some cases cut the space consumption more than 50 %, though typically it is 20 %, I think. The upcoming zip-compression will normally save 40 % of space.
The 17 MB 'free space' in the InnoDB data file probably came from the 'undo log' (= rollback segment). 4.0.16 runs the ALTER as one huge transaction. MySQL-4.1.8 and later do a commit in ALTER TABLE at every 10 000 rows. That would cut the undo log usage to less than 1 MB in the conversion.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php