Performing Table and Database Operations in phpMyAdmin 3.3.x for Effective MySQL Management

Mastering phpMyAdmin 3.3.x for Effective MySQL Management

A complete guide to get started with phpMyAdmin 3.3 and master its features The best introduction to phpMyAdmin available Written by the project leader of phpMyAdmin, and improved over several editions A step-by-step tutorial for manipulating data with phpMyAdmin Learn to do things with your MySQL database and phpMyAdmin that you didn’t know were possible!

Introduction

Various links that enable table operations have been put together on the Operations subpage of the Table view. Here is an overview of this subpage:

Maintaining a table

During its lifetime, a table repeatedly gets modified and is therefore continually growing and shrinking. In addition, outages may occur on the server, leaving some tables in a damaged state.

Using the Operations subpage, 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 alright, 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 (see the third bullet).
• 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 performed periodically (in case data has changed in the table), in order 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.
• Optimize table: This is useful when the table contains overheads. After massive deletions of rows or length changes for VARCHAR fields, 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 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.
• 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.

The operations are based on the available underlying MySQL queries—phpMyAdmin only calls those queries.

Changing table attributes

Table attributes are the various properties of a table. This section discusses the settings for some of them.

Table storage engine

The first attribute that we can change is called Storage Engine.

This controls the whole behavior of the table—its location (on-disk or in-memory), the index structure, and whether it supports transactions and foreign keys. The drop-down list varies depending on the storage engines supported by our MySQL server.

Changing a table’s storage engine may be a long operation if the number of rows is large.

Table comments

This allows us to enter comments for the table:

These comments will be shown at appropriate places—for example, in the navigation panel, next to the table name in the Table view, and in the export file. Here is what the navigation panel looks like when the $cfg[‘ShowTooltip’] parameter is set to its default value of TRUE:

The default value (FALSE) of $cfg[‘ShowTooltipAliasDB’] and $cfg[‘ShowTooltipAliasTB’] produces the behavior we saw earlier—the true database and table names are displayed in the navigation panel and in the Database view for the Structure subpage. Comments appear when the cursor is moved over a table name. If one of these parameters is set to TRUE, the corresponding item (database names for DB and table names for TB) will be shown as a tooltip instead of the names. This time, the mouseover box shows the true name for the item. This is convenient when the real table names are not meaningful.

There is another possibility for $cfg[‘ShowTooltipAliasTB’]—the ‘nested’ value. Here is what happens if we use this feature:

• The true table name is displayed in the navigation panel
• The table comment (for example project__) is interpreted as the project name and is displayed as it is