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.