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.

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.

2

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

3

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

4

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

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.

About the Author

Jeffrey Ober is a full-time freelance writer with over 20 years of experience with computers and the computer industry, and 10 years of experience in education. He has a master's degree in information systems and is also a professional sports photographer.