I attempted to add an index to a date field in a table (MyISAM) having about 4,000 rows, using PHPMyAdmin. PHPMyadmin froze, finally timed out. Now, even after rebooting the server, the MySQL (MariaDB) server is using 100% cpu, and is almost unresponsive. For example, a query to return 400 rows in a small table took 48 seconds, where usually it would be less than 1 sec.
It has been at least 15-20 minutes since I attempted this update.
Show ProcessList;
shows several queries waiting on table locks, but does not show the UPDATE TABLE
query which would have been adding the index.
Does it usually take some time after adding an index for the index to be built, and is MySQL usually very unresponsive during this time? How can I check the status, or stop the process?
mysqlcheck
reports all tables "OK".
SOLUTION: I had added an index on a DateTime field in another MyISAM table with which I was joining this table (it had about 25k rows). For some reason, that index prevented the query from completing - not sure what was up. I deleted the index and everything was fine. Both tables checked out OK, so it wasn't corrupted. Not sure how anyone would ever trace this type of issue without backtracking the previous schema modifications one at a time. There were no pertinent errors messages to point to the issue.