Menu
Why is it taking more than an hour to simply update this table to add a column? This table has 15M rows. It has 2 indexes and a single key primary key. The ALTER TABLE query has been in "copy to tmp table" state for 1 hour 15 minutes now.
ALTER TABLE `frugg`.`item_catalog_map` ADD COLUMN `conversion_url` TEXT NULL DEFAULT NULL Table:
mysql> describe item_catalog_map; +------------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+---------------+------+-----+---------+-------+ | catalog_unique_item_id | varchar(255) | NO | PRI | NULL | | | catalog_id | int(11) | YES | MUL | NULL | | | item_id | int(11) | YES | MUL | NULL | | | price | decimal(10,2) | YES | | 0.00 | | +------------------------+---------------+------+-----+---------+-------+ mysql> show index from item_catalog_map; +------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+ | item_catalog_map | 0 | PRIMARY | 1 | catalog_unique_item_id | A | 15485115 | NULL | NULL | | BTREE | | | item_catalog_map | 1 | IDX_ACD6184FCC3C66FC | 1 | catalog_id | A | 18 | NULL | NULL | YES | BTREE | | | item_catalog_map | 1 | IDX_ACD6184F126F525E | 1 | item_id | A | 15485115 | NULL | NULL | YES | BTREE | | +------------------+------------+----------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+ 
930
Your ALTER TABLE statement implies mysql will have to re-write every single row of the table including the new column. Since you have more than 2 million rows, I would definitely expect it takes a significant amount of time, during which your server will likely be mostly IO-bound.
Just use ALTER TABLE as usual, it'll be mostly instant for renames and index drops, and reasonably fast for index addition (as fast as reading all the table once). If using 5.1+, and the InnoDB plugin is enabled, adding/removing indices will be online as well.
Another way to get fast updates is to delay updates and then do many updates in a row later. Performing multiple updates together is much quicker than doing one at a time if you lock the table. For a MyISAM table that uses dynamic row format, updating a row to a longer total length may split the row.
In all likelihood, the table does not fit into memory on your machine, so this results in about 1300 accesses to the files on disk. That explains why you are seeing a lag time of several seconds. Another solution is to ensure that the data tables themselves fit into memory.
MySQL’s ALTER TABLE performance can become a problem with very large tables. MySQL performs most alterations by making an empty table with the desired new structure, inserting all the data from the old table into the new one, and deleting the old table. This can take a very long time, especially if you’re short on memory and the table is large and has lots of indexes. Many people have experience with ALTER TABLE operations that have taken hours or days to complete.
Anyway if you need to proceed with alter table, maybe the following resources could help you:
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
