From: Wm Mussatto
Date: June 17 2011 4:02pm
Subject: RE: optimization strategies based on file-level storage
List-Archive: http://lists.mysql.com/mysql/225287
Message-Id: <37787804cee7b64fd9af172b0a9a032b.squirrel@secure2.csz.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;boundary="----=_20110617090219_57342"

------=_20110617090219_57342
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit



On Fri, June 17, 2011 07:11, Jerry Schwartz wrote:
>>-----Original Message-----
> <snip>
> 
>>What I was really trying to figure out was why it takes me 4
hours to
>>add a new column to my 22-million-row table, and
whether a different
>>table design can avoid that problem. 
That reply in the forum says,
>>"ALTER TABLE ... ADD
COLUMN will always copy the entire table over,
>>and rebuild
all the indexes. (And, effectively, do an OPTIMIZE.) Do
>>_not_
leave space for extra columns, it won't help."  I'm about to
>>reply and point out the trick that you suggested to me: create
dummy
>>columns early and then just rename them later :)
>>
>>         -Bennett
>>
> [JS]
They will be too small, or the wrong type, or there won't be enough
> of
> them. Based upon 30+ years of database design, I'd bet
money on it. ;-)
> 
> Regards,
> 
> Jerry
Schwartz
> Global Information Incorporated
The only
"alternative design" would be to create another table with the
added columns and a common key field and then lock the primary table and
populate it with the keys from the original table, and I'm not convinced
that would be any faster or less disruptive.
------
William R.
Mussatto
Systems Engineer
http://www.csz.com
909-920-9154

------=_20110617090219_57342--