Re: Almost whole table in single index: is there a better way?

[Aftab Khan]

>I did some benchmarks using InnoDB, and found that the database file was substantially larger (more than double) than the MyISAM.

This is expected as InnoDB footprints are 2x-3x of MyISAM.

>I also had concerns about disk space not being freed up when the old data/partitions are pruned with InnoDB.
It won't be an issue if you use innodb_file_per_table=1. With multiple tablespaces enabled (i.e.innodb_file_per_table=1), InnoDB stores each newly created table into its own tbl_name.ibd file in the database directory where the table belongs. The innodb_file_per_table option affects only table creation, not access to existing tables.

http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html


How much RAM do you have on the server?
Can we see MySQL options file i.e. /etc/my.cnf?