Message-ID: <45AFA36E.2040603@tweakers.net>
Date: Thu, 18 Jan 2007 17:42:22 +0100
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
To: Scott Marlowe <smarlowe@g2switchworks.com>
CC: Adam Rich <adam.r@sbcglobal.net>,
	'Bricklen Anderson' <banderson@presinet.com>,
	pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
References: <090501c73a90$7f154330$6400a8c0@dualcore>	
	<45AF4ACA.5060805@tweakers.net>
	<1169137253.9586.79.camel@state.g2switchworks.com>
In-Reply-To: <1169137253.9586.79.camel@state.g2switchworks.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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