MySQL: add a field to a large table

Question

i have a table with about 200,000 records. i want to add a field to it:

 ALTER TABLE `table` ADD `param_21` BOOL NOT NULL COMMENT 'about the field' AFTER `param_20`

but it seems a very heavy query and it takes a very long time, even on my Quad amd PC with 4GB of RAM.

i am running under windows/xampp and phpMyAdmin. does mysql have a business with every record when adding a field? or can i change the query so it makes the change more quickly?

Answer 1

You add a NOT NULL column, the tuples need to be populated. So it will be slow...

Answer 2

This touches each of 200.000 records, as each record needs to be updated with a new bool value which is not going to be null.

So; yes it's an expensive query... There is nothing you can do to make it faster.

Answer 3

MySQL will, in almost all cases, rebuild the table during an ALTER**. This is because the row-based engines (i.e. all of them) HAVE to do this to retain the data in the right format for querying. It's also because there are many other changes you could make which would also require rebuilding the table (such as changing indexes, primary keys etc)

I don't know what engine you're using, but I will assume MyISAM. MyISAM copies the data file, making any necessary format changes - this is relatively quick and is not likely to take much longer than the IO hardware can get the old datafile in and the new on out to disc.

Rebuilding the indexes is really the killer. Depending on how you have it configured, MySQL will either: for each index, put the indexed columns into a filesort buffer (which may be in memory but is typically on disc), sort that using its filesort() function (which does a quicksort by recursively copying the data between two files, if it's too big for memory) and then build the entire index based on the sorted data.

If it can't do the filesort trick, it will just behave as if you did an INSERT on every row, and populate the index blocks with each row's data in turn. This is painfully slow and results in far from optimal indexes.

You can tell which it's doing by using SHOW PROCESSLIST during the process. "Repairing by filesort" is good, "Repairing with keycache" is bad.

All of this will use AT MOST one core, but will sometimes be IO bound as well (especially copying the data file).

** There are some exceptions, such as dropping secondary indexes on innodb plugin tables.