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.
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.
About Calvin Correli
I've spent the last 17 years learning, growing, healing, and discovering who I truly am, so that I'm now living every day aligned with my life's purpose.
0 comments
Leave a comment