The first thing you need to determine about col2 is if it can be a PRIMARY KEY.
Run this query
SELECT COUNT(1),col2 FROM table GROUP BY col2 HAVING COUNT(1) > 1;
If nothing comes back, then col2 can be a UNIQUE KEY. If even one row comes back, then col2 cannot be a UNIQUE KEY. You can create an index on it.
Since this query would take a while without an index, just go ahead and index it
ALTER TABLE table ADD INDEX col2 (col2);
but that might take a very long time.
You may need a temp table to accomplish this:
Step 01) Create a table that will hold the one million col2 values
DROP TABLE IF EXISTS col2values;
CREATE TABLE col2values ENGINE=InnoDB SELECT col2 FROM table WHERE 1=2;
ALTER TABLE col2value ADD PRIMARY KEY (col2);
Step 02) You can load the col2values table with the one million col2 values
Step 03) Index col2
CREATE TABLE table2 LIKE table;
ALTER TABLE table2 ADD INDEX col2 (col2);
INSERT INTO table2 SELECT * FROM table;
DROP TABLE table;
ALTER TABLE table2 RENAME table;
Step 04) Perform update using INNER JOIN
UPDATE col2values A
INNER JOIN table B
USING (col2)
SET B.col1=1;
In the above steps
- Step 03 needs to be done only once
- Step 03 is done with 5 lines rather than
ALTER TABLE table ADD INDEX col2 (col2);
because it would require a full rollback operation should anything go wrong.
- All other steps are your bulk insert steps going forward
Give it a Try !!!
If you want to prevent any ALTER TABLE ... ENGINE=InnoDB;
from executing on a Slave, just run this on the Master:
SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;
What this does is prevent the Master from recording the ALTER TABLE ... ENGINE=InnoDB;
in its binary logs. Since the Slave only replicates from the binary logs of the Master, just don't record the commands that you do not want replicated.
If you have multiple tables to convert and not replicate, then you can do this:
SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
ALTER TABLE ... ENGINE=InnoDB;
.
.
.
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;
Best Answer
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
Please note the following:
I dropped source_persona_index because it is the first column in 4 other indexes
I dropped target_persona_index because it is the first column in 2 other indexes
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:
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.
COUNT(DISTINCT sent_at)
> 4,000,000ALTER TABLE s_relations_new DROP INDEX sent_at_index;
COUNT(DISTINCT message_id)
> 4,000,000ALTER TABLE s_relations_new DROP INDEX message_id_index;
COUNT(DISTINCT target_object_id)
> 4,000,000ALTER 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
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):
Next, login to mysql and load those last rows:
Then, restart mysql normally
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:
Give it a Try !!!
CAVEAT : Once you are satisfied with this operation, you can drop the old table at your earliest convenience: