"coyote2002" <co********@interia.pl> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.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 "<datadir>/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.
