calvin-brand-light-mode
calvin-brand-dark-mode
∕
⌘K
About Calvin Podcast We Can Do This Book Speaking
aAbout Calvin
Podcast
wWe Can Do This Book
sSpeaking
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.
Previous How to set a default form builder in Rails 3.1 while letting it be autoloaded
Next Github textmate bundle

Comments

Write a comment...
00:00 Recording...
calvin-brand-light-mode
calvin-brand-dark-mode
1 Chestnut Hill Plaza #1139
Newark, DE 19713
United States
Copyright © 2026 Calvin Correli
powered by