Databases Reference
In-Depth Information
Maintaining a table
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
JOIN
operations 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 improve
JOIN
efficiency.
•
Repair
table
: Repairs any corrupted data for tables in the
MyISAM
and
ARCHIVE
engines. Note that a table might be so corrupted that we cannot
even go into
Table
view 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
InnoDB
table
fragment its index. The table should be periodically defragmented for faster
data retrieval. This operation causes MySQL to rebuild the table and only
applies to
InnoDB
.
•
Optimize
table
: This is useful when the table contains overheads. After
massive deletions of rows or length changes for
VARCHAR
columns, lost
bytes remain in the table. phpMyAdmin warns us in various places (for
example, in the
Structure
view) 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 the
MyISAM
,
InnoDB
, and
ARCHIVE
engines.
•
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.
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
.
Search WWH ::
Custom Search