At 12:02 AM 2/22/2007, Scott Marlowe wrote:
You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables. Don't forget that if you've got a really
Oh yeah, that reminds me. "rewriting the whole thing" means in most
cases the _entire_ table is temporarily _duplicated_ (with all the
associated increased space requirements)![1]
WORSE: This happens if you are creating or deleting indexes, or even
changing a column definition!
So say you have a 40GB table, and have 30GB free space. Life is good
right? Then someone makes a reasonable request - Big Boss wants an
important report sped up, and it turns out you just need to create an
index. Enjoy :).
Running low on space and think you can get more space by deleting
some unused indexes? Probably not a good idea!
And even if disk space is cheap, IO bandwidth usually isn't...
Regards,
Link.
[1] "If you use any option to ALTER TABLE other than RENAME, MySQL
always creates a temporary table"
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
MySQL: the PHP of databases.
