In the previous chapters, we dealt mostly with table columns. In this chapter, we will learn how to perform some operations that influence tables or databases as a whole. We will cover table attributes and how to modify them, and will also discuss multi-table operations.
Various links that enable table operations have been put together on the Operations page of the Table view. Here is an overview of this page:
During its lifetime, a table repeatedly gets modified and is, therefore, continually growing and shrinking. Outages may occur on the server, leaving some tables in a damaged state.
Using the Operations page, we can perform various operations, which are listed next. However, not every operation is available for every storage engine.
- Check table: Scans all rows to verify that deleted links are correct. A checksum is also calculated to verify the integrity of the keys. If everything is all right, we will obtain a message stating OK or Table is already up to date; if any other message shows up, it's time to repair this table (refer to the Repair table bullet point).
- Analyze table: Analyzes and stores the key distribution; this will be used on subsequent
JOINoperations to determine the order in which the tables should be joined. This operation should be periodically done (in case data has changed in the table) to improveJOINefficiency. - Repair table: Repairs any corrupted data for tables in the
MyISAMandARCHIVEengines. Note that a table might be so corrupted that we cannot even go intoTableview for it! In such a case, refer to the Multi-table operations section for the procedure to repair it. - Defragment table: Random insertions or deletions in an
InnoDBtable fragment its index. The table should be periodically defragmented for faster data retrieval. This operation causes MySQL to rebuild the table and only applies toInnoDB. - Optimize table: This is useful when the table contains overheads. After massive deletions of rows or length changes for
VARCHARcolumns, lost bytes remain in the table. phpMyAdmin warns us in various places (for example, in theStructureview) if it feels the table should be optimized. This operation reclaims the unused space in the table. In the case of MySQL 5.x, the relevant tables that can be optimized use theMyISAM, InnoDB, andARCHIVEengines. - Flush table: This must be done when there have been many connection errors and the MySQL server blocks further connections. Flushing will clear some internal caches and allow normal operations to resume.
Note
The operations are based on the available underlying MySQL queries—phpMyAdmin only calls those queries. More details are available at http://dev.mysql.com/doc/refman/5.5/en/table-maintenance-sql.html.