Fast index creation in MySQL with InnoDB

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.


There are no comments yet. Be the first one to leave a comment!

Leave a comment