Table Maintenance

Optimizing a table

Optimizing a table will automatically defragment a MySQL table and it is very useful for the database when your table is updated or deleted some records. Optimizing tables will work when: first, when there was a deletion of rows in a table it repairs the table. Second, when index pages are not sorted this function used to sort the index page. Lastly, when the table’s statistics are not up to date it is used to update the table’s statistic.

Checking a table

Checking a table is used for checking errors and used to verify data and table handler. Checking command in MySQL will check the file size, record deletion, index reference, data record references index and record links. It also checks the table if it is corrupted, it will find bugs for us to determine what was the error and it will be written in error log. It compresses all information sent between the client and the server.

Repairing a table

Repairing a table is used for repairing the possible corrupted table. If you want to repair corrupted tables, you need to back it up first or move your data file to safe place because if you fail the data might be in worse shape or may become unsalvageable.  It restores index file errors and also used if the records file is corrupted or crashed.  It makes sure that the table is still working, and then it indicates whether the table was valid or whether corrupted.

Analyzing a table

Analyzing a table is used to update index information for a table to improve performance and MySQL can make a better decision on how to join tables. It used to analyze and store the key distribution to a table. It is also used for deciding the order in which tables are joined when we are performing a join on something other than a constant. It is used when connected to a database on the server.