Re: Configuration Advice

On 18-1-2007 17:20 Scott Marlowe wrote:
>> Besides that, mysql rewrites the entire table for most table-altering
>> statements you do (including indexes).
>
> Note that this applies to the myisam table type. innodb works quite
> differently. It is more like pgsql in behaviour, and is an mvcc storage

Afaik this is not engine specific and also applies to InnoDB. Here is
what the MySQL-manual sais about it:
"In most cases, ALTER TABLE works by making a temporary copy of the
original table. The alteration is performed on the copy, and then the
original table is deleted and the new one is renamed. While ALTER TABLE
is executing, the original table is readable by other clients. Updates
and writes to the table are stalled until the new table is ready, and
then are automatically redirected to the new table without any failed
updates."

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

If it were myisam-only they sure would've mentioned that. Besides this
is the behaviour we've seen on our site as well.

Since 'create index' is also an alter table statement for mysql, this
also applies for adding indexes.

Best regards,

Arjen