List:General Discussion« Previous MessageNext Message »
From:Stefan Hinz Date:July 23 2003 10:01pm
Subject:Re[2]: Is it necessary to compress tables?
View as plain text  
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 <hinz@stripped>
  iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]

Thread
Is it necessary to compress tables?Jim McAtee23 Jul
  • Re: Is it necessary to compress tables?Jim McAtee23 Jul
    • Re: Is it necessary to compress tables?Dan Nelson23 Jul
    • Re[2]: Is it necessary to compress tables?Stefan Hinz24 Jul
    • Replication stopped without reasontrashMan24 Jul
      • Re: Replication stopped without reasonDominicus Donny24 Jul
        • R: Replication stopped without reasontrashMan24 Jul
          • Re: R: Replication stopped without reasonEgor Egorov25 Jul
            • R: R: Replication stopped without reasontrashMan25 Jul
  • Re: Is it necessary to compress tables?Jim McAtee24 Jul
Re: Replication stopped without reasonInfo26 Jul