On Thu, Aug 5, 2010 at 5:07 PM, Daevid Vincent <dae...@daevid.com> wrote: > 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? > > r...@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...|
Having significant amount of overhead for unused columns will without doubt harm performance significantly for certain operations. Altering .frm files should is always be tried on a non-prod box before even considering using it on prod. There are some well known use cases (adding enums values, enlarging varchar columns) where altering a .frm is useful, but it should always be considered very dangerous. You could of course consider using PostgreSQL which would only need a very brief exclusive lock for adding a default null column... -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
