How to Rebuild Indexes on MYSQL

By Jeffrey Ober

When a system error happens to a mySql database, or when a table simple receives a large amount of incorrectly formatted data, the table indexes may become corrupt. The mySql system will inform a user that the table needs to be repaired when a CHECK TABLE command is run on the table. The command to verify the table needs repairing looks like this: CHECK TABLE tablename FOR UPGRADE. If the mySql system responds with "Table upgrade required," then the table needs the indexes rebuilt.

Step 1

Using telnet, ssh, or a local machine, connect to the mySql server and log into the mySql command-line tool. This can be done with mysql -p.

Step 2

Use the CHECK TABLE tablename FOR UPGRADE command to verify the table needs to be repaired.

Step 3

Exit the mysql command-line tool by typing x and pressing the "Enter" key. You will return to the shell command prompt.

Step 4

Dump the table with the mysqldump command: mysqldump databaseName tableName > dump.sql.

Step 5

Re-create the table in the database with the mysql command: mysql databaseName < dump.sql. This will rebuild the table and the table indexes.

References

Writer Bio