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
