MySQL sucks at large tables. Sorry if you’ve come here thinking that I, in any way, like MySQL. That said, I have to look after some sizeable databases which are on MySQL for various reasons - so needs must.
I recently needed to sort out an issue with indexes on a table. I needed to drop one, and add a couple more. Snag? The table has 3.6 million rows and as such I would call it a large table.
Normally, I guess, you would do something like:
ALTER TABLE foo ADD INDEX (my_column);
ALTER TABLE foo DROP INDEX old_index;
This doesn’t work when you have a lot of rows. Well, it does… if you have a few hours to spare. Let’s look at why.
When you add an index to a pre-existing table, MySQL has to go look through the entire table to populate the new index. When you drop an index on a pre-existing table, it loops through every row depopulating the index before finally dropping it.
So, here’s what I did to speed things up:
-
SELECT * FROM foo INTO OUTFILE 'foo_outfile.dmp';
-
CREATE TABLE foo_a LIKE foo;
-
ALTER TABLE foo_a ADD INDEX (my_column);
-
ALTER TABLE foo_a DROP INDEX old_index;
-
LOAD DATA INFILE 'foo_outfile.dmp' INTO TABLE foo_a;
-
TRUNCATE TABLE foo; DROP TABLE foo;
-
ALTER TABLE foo_a RENAME foo;
Why TRUNCATE before DROP? That’s easy, DROP tries to delete each row. To delete each row the index has to be updated… 3.6M times! TRUNCATE trashes everything, then you’re dropping an empty table!