List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:August 6 2010 12:07am
Subject:Possible tricks to ALTER on huge tables?
View as plain text  
Driving to work today, I had an epiphany thought, but wanted to see if
anyone could prove my theory or not.

We currently have some tables that are approaching 1 BILLION rows (real
Billion, with nine zeros, not that silly six zero version). Trying to do an
"ALTER" on them to add a column can sometimes take hours.

I'm wondering if we had the foresight to create the tables, and then tack
on extra "dormant" columns of various common types, such as:

future_uint int(10) unsigned null,
future_int int(10) signed null,
future_var varchar(255) null,
Etc.

So basically they'd be unused, then when we wanted a "new" column of that
type, we would just "rename" the dormant one. 

I'm not sure if mySQL is smart enough to realize that if the schema
definition for a column is identical, then it's just a simple rename, or if
it treats any change the same and will still take hours to complete (if so,
perhaps there's an optimization for you mysql developer team)

Another option I considered, was is it possible to just go in with a hex
editor and rename the field in the .frm file? Is there some kind of .frm
editor available anywhere?

root@mypse:/var/lib/mysql/agis_core# hexdump -C country.frm
00001000  01 01 00 00 0a 00 00 00  02 00 01 00 00 00 01 80
|................|
00001010  02 00 00 12 00 02 00 ff  50 52 49 4d 41 52 59 ff
|........PRIMARY.|
00001020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00
|................|
*
000013f0  00 00 00 00 00 00 00 00  00 00 00 00 06 00 49 6e
|..............In|
00001400  6e 6f 44 42 00 00 00 00  00 00 00 00 00 00 00 00
|noDB............|
00001410  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00
|................|
*
00002150  04 00 0b 69 64 5f 63 6f  75 6e 74 72 79 00 05 00
|...id_country...|
00002160  05 69 73 6f 32 00 06 00  05 69 73 6f 33 00 07 00
|.iso2....iso3...|
00002170  0d 63 6f 75 6e 74 72 79  5f 6e 61 6d 65 00 04 0b
|.country_name...|
00002180  05 05 00 02 00 00 12 00  0f 00 00 02 c0 00 00 05
|................|
00002190  05 06 06 00 04 00 00 00  80 00 00 00 fe c0 00 00
|................|
000021a0  06 05 09 09 00 0a 00 00  00 80 00 00 00 fe c0 00
|................|
000021b0  00 07 0d 42 fd 02 13 00  00 00 00 00 00 00 0f c0
|...B............|
000021c0  00 00 ff 69 64 5f 63 6f  75 6e 74 72 79 ff 69 73
|...future_var...|
000021d0  6f 32 ff 69 73 6f 33 ff  63 6f 75 6e 74 72 79 5f
|...future_int...|

Thread
Possible tricks to ALTER on huge tables?Daevid Vincent6 Aug
  • Re: Possible tricks to ALTER on huge tables?Buford Tannen6 Aug
  • Re: Possible tricks to ALTER on huge tables?Rob Wultsch6 Aug
    • RE: Possible tricks to ALTER on huge tables?Daevid Vincent6 Aug
  • Re: Possible tricks to ALTER on huge tables?Jigal van Hemert6 Aug
    • Re: Possible tricks to ALTER on huge tables?Andy Wallace6 Aug