0

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.

  • That is a strong reason for moving from MyISAM to InnoDB. More discussion: mysql.rjweb.org/doc.php/myisam2innodb – Rick James Oct 25 '19 at 0:10
  • Questions like this get a lot more traction in stackoverflow.com – Rick James Oct 25 '19 at 0:10
  • @RickJames I would like to do that, but I have some tables which need fulltext indexes. My server is Centos 7 and Virtualmin, with MariaDB 5.5.60 and it's not recommended to update MariaDB to v10 until the next Virtualmin release. – Ryan Griggs Oct 25 '19 at 0:55
  • @RickJames I never know which section to post in. I just guessed that this was more "admin-related" than programming related, so I went with ServerFault. – Ryan Griggs Oct 25 '19 at 0:56
  • Yeah, the lines between the forums is vague. My second choice would be where DBA questions should go: dba.stackexchange.com. My point is, database questions get a lot more traffic at those two sites, much more than at ServerFault. – Rick James Oct 26 '19 at 0:11
0

Back in 5.5 days, adding an index required rebuilding the table and all its indexes. That includes your FULLTEXT index. Rebuilding it is quite a CPU chore.

Newer versions have FULLTEXT for InnoDB.

Newer versions have ways to ADD INDEX without rebuilding everything else.

Etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.