Optimizing tables

Hi,
I have big problem with mysql 4.0.18 . Because my database is growing
up, every night records that are older than some date are deleted. But
size of database on disk isn`t smaller after this operation. There are
some solutions to free space by OPTIMIZE TABLE command, but this
command when executes, does temporary copy of database. How can I free
memory and avoid doing temporary copy of database ( because I don`t
enough free space for this ).

Best Regards

Re: Optimizing tables

"coyote2002" wrote in message
news:1143204343.404618.101040@t31g2000cwb.googlegroups.com.. .
> How can I free
> memory and avoid doing temporary copy of database ( because I don`t
> enough free space for this ).

This is a common complaint about MySQL. See the comments in this bug:
http://bugs.mysql.com/bug.php?id=1341

The bug is unresolved at this time. The recommendation is to use ALTER
TABLE (even one that changes nothing like ALTER TABLE myTable TYPE=InnoDB
when the table is already InnoDB) to rebuild the table. But this still
doesn't shrink the file, it just defragments the space so subsequent updates
to the table can utilize the free space more efficiently.

AFAIK, the only way to shrink the files is to back up your tables (or entire
database), DROP them, and then restore them from the backups.

In the case of InnoDB, you may have to back up _all_ databases, manually
remove the "/ibdata1" file, and then restore the databases. This
is because InnoDB stores all tables and indexes in that one file.

In the case of MyISAM, you can do this on a table-by-table basis, since each
table is stored in a separate file on disk. InnoDB has an option
"innodb_file_per_table" that you can enable to make it store tables
similarly.

This is a big inconvenience when using MySQL, but several other RDBMS brands
suffer from similar limitations. It's hard to shrink a database file as an
online task. Depending on the quantity of data, it could take a long time
to do that, and it interrupts other clients' access to the database.

Regards,
Bill K.