Back
Fast index creation in MySQL with InnoDB
Other·Calvin Correli·Nov 1, 2011· 2 minutes
I hunted around the web for hours and hours and hours trying to figure this out. I have a table with 1.4M rows, and I need to add some indices to it, and to drop one that was created wrong. (It started with the primary key - bad idea!) One my laptop, creating the index took a few seconds. On the server, it took over 20 minutes, then I killed it. Couldn't afford to take zenbilling down for that long. When doing a "show processlist", it told me it was doing a "Copy to tmp table", which took forever. I searched for "copy to tmp table innodb create index" and permutations of that, and came up with nothing but other people's frustrations. I looked at every single variable in "show variables" and compared them, tweaked them on the server, but nothing changed. Only when I googled "fast index creation innodb" did I come up with this. Turns out that the server was running MySQL 5.0 and I was running 5.5 locally. If you install MySQL 5.1 (the latest available in debian) and install the InnoDB plugin, then you can create indices in a snap. Don't forget to configure MySQL to use the InnoDB plugin instead of the built-in version.