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.
DROP INDEX culprit ON bigtable
But the ultimate convict still eludes me. Any ideas? I still need to add those indexes.