Re[2]: Is it necessary to compress tables?

[Stefan Hinz]

Jim,

Does MySQL automatically handle deleted row cleanup, or is it necessary to
periodically do this manually for tables with a lot of deletions? If it's
manual, what are the SQL commands to do this?
Depends on which storage engine you are using. If you are using InnoDB
tables, there's no need to "vacuum" tables that way, as InnoDB will do
that automatically. If you use MyISAM tables, however, you might be
able to speed up things quite a bit by using the OPTIMIZE TABLE
command at regular intervals. That SQL command will reclaim unused
space that results from deleting rows, and it will speed up table
lookups:

http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html
Also, what happens when changes are made to a field definition? From, say,

int

to smallint, or tinyint to int, or varchar(255) to varchar(10)? Do temporary
tables get created and the data copied over, or does unused space end up in

the

data files, to be collected later?

This sentence from the manual should make that clear: "ALTER TABLE
works by making a temporary copy of the original table." You can find
details here:

http://www.mysql.com/doc/en/ALTER_TABLE.html

Regards,
--
Stefan Hinz <hi**@iConnect.de>
iConnect GmbH <http://iConnect.de>
Heesestr. 6, 12169 Berlin (Germany)
Telefon: [phone] Fax: [phone]

[filter fodder: sql, mysql, query]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw