Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices.

I want to change the name of one of the columns and add a few more indices.

  • What is the fastest way to do it (assuming I could suffer even downtime - the server is an unused slave)?
  • Is a "plain" alter table, the fastest solution?

At this time, all I care about is speed :)

share|improve this question
Please SHOW CREATE TABLE tblname\G, show the column that needs to changed, the datatype of the column, and the new name for the column. – RolandoMySQLDBA Dec 27 '11 at 4:09
here it is: the column that need to be renamed is sent_at and to add it a few more indices – Ran Dec 27 '11 at 8:09
sent_at needs to be renamed to what ? – RolandoMySQLDBA Dec 27 '11 at 12:03
lets say: new_sent_at – Ran Dec 27 '11 at 14:36
up vote 7 down vote accepted

One sure way to speed up an ALTER TABLE is to remove unnecessary indexes

Here are the initial steps to load a new version of the table

CREATE TABLE s_relations_new LIKE s_relations;
# Drop Duplicate Indexes
ALTER TABLE s_relations_new
    DROP INDEX source_persona_index,
    DROP INDEX target_persona_index,
    DROP INDEX target_persona_relation_type_index

Please note the following:

  • I dropped source_persona_index because it is the first column in 4 other indexes

    • unique_target_persona
    • unique_target_object
    • source_and_target_object_index
    • source_target_persona_index
  • I dropped target_persona_index because it is the first column in 2 other indexes

    • target_persona_relation_type_index
    • target_persona_relation_type_message_id_index
  • I dropped target_persona_relation_type_index because the first 2 columns are also in target_persona_relation_type_message_id_index

OK That takes care of unnecessary indexes. Are there any indexes that have low cardinality? Here is the way to determine that:

Run the following queries:

SELECT COUNT(DISTINCT sent_at)               FROM s_relations;
SELECT COUNT(DISTINCT message_id)            FROM s_relations;
SELECT COUNT(DISTINCT target_object_id)      FROM s_relations;

According to your question, there are about 80,000,000 rows. As a rule of thumb, the MySQL Query Optimizer will not use an index if the cardinality of the selected columns is greater that 5% of the table row count. In this case, that would be 4,000,000.

  • If COUNT(DISTINCT sent_at) > 4,000,000
    • then ALTER TABLE s_relations_new DROP INDEX sent_at_index;
  • If COUNT(DISTINCT message_id) > 4,000,000
    • then ALTER TABLE s_relations_new DROP INDEX message_id_index;
  • If COUNT(DISTINCT target_object_id) > 4,000,000
    • then ALTER TABLE s_relations_new DROP INDEX target_object_index;

Once the usefulness or uselessness of those indexes have been determined, you can reload the data

# Change the Column Name
# Load the Table
ALTER TABLE s_relations_new CHANGE sent_at sent_at_new int(11) DEFAULT NULL;
INSERT INTO s_relations_new SELECT * FROM s_relations;

That's it, right? NOPE !!!

If your website has been up this whole time, there may have INSERTs running against s_relations during the loading of s_relations_new. How can you retrieve those missing rows?

Go find the maximum id in s_relations_new and append everything after that ID from s_relations. To assure that the table is frozen and used only for this update, you must have a little downtime for the sake of getting those last rows that were inserted into s_relation_new. Here is what you do:

In the OS, restart mysql so that no one else can log in but root@localhost (disables TCP/IP):

$ service mysql restart --skip-networking

Next, login to mysql and load those last rows:

mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Then, restart mysql normally

$ service mysql restart

Now, if you cannot take mysql down, you will have to do a bait-and-switch on s_relations. Just login to mysql and do the following:

mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations_old WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Give it a Try !!!

CAVEAT : Once you are satisfied with this operation, you can drop the old table at your earliest convenience:

mysql> DROP TABLE s_relations_old;
share|improve this answer

The correct answer depends on the version of the MySQL engine you're using.

If using 5.6+, renames and adding/removing indices are performed online, i.e. without copying all the table's data.

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. Not sure about renames.

If using older version, ALTER TABLE is still the fastest—but will probably be horribly slow because all of your data will be re-inserted to a temporary table under the hood.

Finally, time for myth debunking. Unfortunately I don't have enough karma here to comment on answers, but I feel it's important to correct the most voted answer. This is wrong:

As a rule of thumb, the MySQL Query Optimizer will not use an index if the cardinality of the selected columns is greater that 5% of the table row count

It's actually the other way around.

Indices are useful to select few rows, so it's important they have high cardinality, which means many distinct values and statistically few rows with the same value.

share|improve this answer
Link to InnoDB plugin documentation (couldn't paste due to rep limits). – mezis Sep 6 '13 at 14:48
On MySQL 5.5 I found RENAME TABLE instant (as expected) but a CHANGE COLUMN to rename the primary key did a full copy... 7 hours! Possibly only because it was the primary key? Not good. – KCD Dec 4 '13 at 23:46

For the column rename,

ALTER TABLE tablename CHANGE columnname newcolumnname datatype;

should be fine and not carry any downtime.

For the indexes the CREATE INDEX statement will lock the table. If it's a unused slave as you mentioned, that's not an issue.

One other option would be to create a brand new table that has the proper column names and indexes. Then you could copy all the data into it, then execute a series of

ALTER TABLE RENAME tablename tablenameold;
ALTER TABLE RENAME newtablename tablename;
DROP TABLE tablenameold;

This would minimize the downtime at the cost of temporarily using twice the space.

share|improve this answer
DDL in MySQL is not transactional. Each DDL statement triggers a COMMIT. I wrote about this: – RolandoMySQLDBA Feb 8 '14 at 2:33

I've got this problem too and I used this SQL:

/*on créé la table COPY SANS les nouveaux champs et SANS les FKs */
CREATE TABLE IF NOT EXISTS prestations_copy LIKE prestations;

/* on supprime les FKs de la table actuelle */
ALTER TABLE `prestations`
DROP FOREIGN KEY `fk_prestations_pres_promos`,
DROP FOREIGN KEY `fk_prestations_activites`;

/* on remet les FKs sur la table copy */
ALTER TABLE prestations_copy 
    ADD CONSTRAINT `fk_prestations_activites` FOREIGN KEY (`act_id`) REFERENCES `activites` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    ADD CONSTRAINT `fk_prestations_pres_promos` FOREIGN KEY (`presp_id`) REFERENCES `pres_promos` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

/* On fait le transfert des données de la table actuelle vers la copy, ATTENTION: il faut le même nombre de colonnes */
INSERT INTO prestations_copy
SELECT * FROM prestations;

/* On modifie notre table copy de la façon que l'on souhaite */
ALTER TABLE `prestations_copy`
    ADD COLUMN `seo_mot_clef` VARCHAR(50) NULL;

/* on supprime la table actuelle et renome la copy avec le bon nom de table */
DROP TABLE prestations;
RENAME TABLE prestations_copy TO prestations;

I hope it could help someone



share|improve this answer

I had the same problem with Maria DB 10.1.12, then after reading the documentation I found that there is an option to perform the operation "in-place" which eliminates the copy of the table. With this option the alter table is very fast. In my case it was:

alter table user add column (resettoken varchar(256),
  resettoken_date date, resettoken_count int), algorithm=inplace;

this is very fast. Without the algorithm option it would never terminate.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.