MySQL: Huge Table Not Responding after Adding Index

by Yang Yang on May 18, 2009

Today I’m optimizing some MySQL tables with large number of records – 1 million of them, yeah, I know – , it’s simply impossible to deal with such big chunks of data without proper indexing. So there I was, adding a variety of indexes to a few of the columns.

It’s expected it’d take a few hours to complete with such mega size tables, but what’s not expected is that after 2 days, the index adding seems to be still going on as all the tables are not responding to any queries. I can’t even select the first record. It takes *forever*, both from phpmyadmin and shell mysql.

What’s the catch? I’ve no idea. But let’s at least free those poor tables from choking first. So after dropping the index I was trying to add from mysql command line, all the tables returned normal.

Yeah.

DROP INDEX culprit ON bigtable

But the ultimate convict still eludes me. Any ideas? I still need to add those indexes.

Comments on this entry are closed.

Previous post:

Next post: