In this article by Marc Delisle, 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 also discuss multi-table operations.

Table Maintenance

During the lifetime of a table, it repeatedly gets modified, and so grows and shrinks. Outages may occur on the server, leaving some tables in a damaged state. Using the Operations sub-page, we can perform various operations, but not every operation is available for every table type:

• Check table: Scans all rows to verify that deleted links are correct. Also, a checksum is calculated to verify the integrity of the keys; we should get an 'OK' message if everything is all right.
• 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.
• Repair table: Repairs any corrupted data for tables in the MyISAM and ARCHIVE engines. Note that the 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 is a kind of defragmentation for the table. In MySQL 4.x, this operation works only on tables in the MyISAM, Berkeley (BDB), and InnoDB engines. In MySQL 5.x, it works only on tables in the MyISAM, InnoDB, andARCHIVE engines.
• Flush table: This must be done when there have been lots of 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.

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

Changing Table Attributes

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

Table Type

The first attribute we can change is called Table 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 may vary depending on the table types supported by our MySQL server.

Changing the table type 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 left panel, next to the table name in the Table view and in the export file). Here is what the left panel looks like when the $cfg['ShowTooltip'] parameter is set to its default value of TRUE:

The default value of $cfg['ShowTooltipAliasDB'] and $cfg['ShowTooltipAliasTB'] (FALSE) produces the behavior we have seen earlier: the true database and table names are displayed in the left panel and in the Database view for the Structure sub-page. Comments appear when the mouse pointer is moved over a table name. If one of these parameters is set toTRUE, the corresponding item (database names for DB and table names for TB) will be shown as the tooltip instead of the names. This time, the mouse-over 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 left panel.
• The table comment (for example project__) is interpreted as the project name and is displayed as such.

Table Order

When we Browse a table or execute a statement such as SELECT * from book, without specifying a sort order, MySQL uses the order in which the rows are physically stored. This table order can be changed with the Alter table order by dialog. We can choose any field, and the table will be reordered once on this field. We choose author_id in the example, and after we click Go, the table gets sorted on this field. Reordering is convenient if we know that we will be retrieving rows in this order most of the time. Moreover, if later we use an ORDER BY clause and the table is already physically sorted on this field, the performance should be higher.

This default ordering will last as long as there are no changes in the table (no insertions, deletions, or updates). This is why phpMyAdmin shows the (singly) warning.

After the sort has been done on author_id, books for author 1 will be displayed first, followed by the books for author 2, and so on. (We are talking about a default browsing of the table without explicit sorting.) We can also specify the sort order: Ascending or Descending. If we insert another row, describing a new book from author 1, and then click Browse, the book will not be displayed along with the other books for this author because the sort was done before the insertion.

Table Options

Other attributes that influence the table's behavior may be specified using the Table options dialog:

The options are:

• pack_keys:Setting this attribute results in a smaller index; this can be read faster but takes more time to update. Available for the MyISAM storage engine.
• checksum: This makes MySQL compute a checksum for each row. This results in slower updates, but easier finding of corrupted tables. Available for MyISAM only.
• delay_key_write: This instructs MySQL not to write the index updates immediately but to queue them for later, which improves performance. Available for MyISAM only.
• auto-increment: This changes the auto-increment value. It is shown only if the table's primary key has the auto-increment attribute.

This article has been extracted from: Mastering phpMyAdmin 2.11 for Effective MySQL Management

Increase your MySQL productivity and control by discovering the real power of phpMyAdmin 2.11 Effectively administer your MySQL databases with phpMyAdmin. Manage users and privileges with MySQL Server Administration tools. Get to grips with the hidden features and capabilities of phpMyAdmin. For more information, please visit: http://www.packtpub.com/phpmyadmin-3rd-edition/book

Renaming, Moving, and Copying Tables

The Rename operation is the easiest to understand: the table simply changes its name and stays in the same database. The Move operation (shown in the following screen) can manipulate a table in two ways: change its name and also the database in which it is stored

Moving a table is not directly supported by MySQL, so phpMyAdmin has to create the table in the target database, copy the data, and then finally drop the source table. The Copy operation leaves the original table intact and copies its structure or data (or both) to another table, possibly in another database. Here, the book-copy table will be an exact copy of the book source table. After the copy, we will stay in the Table view for the book table unless we selected Switch to copied table.

The Structure only copy is done to create a test table with the same structure.

Appending Data to a Table

The Copy dialog may also be used to append (add) data from one table to another. Both tables must have the same structure. This operation is achieved by entering the table to which we want to copy the data of the current table and choosing Data only.

For example, we would want to append data when book data comes from various sources (various publishers), is stored in more than one table, and we want to aggregate all the data to one place. For MyISAM, a similar result can be obtained by using the MERGE storage engine (which is a collection of identical MyISAM tables), but if the table is InnoDB, we need to rely on phpMyAdmin's Copy feature.

Multi-Table Operations

In the Database view, there is a checkbox next to each table name and a drop-down menu under the table list. This enables us to quickly choose some tables and perform an operation on all those tables at once. Here we select the book-copy and the book tables, and choose the Check operation for these tables.

We could also quickly select or deselect all the checkboxes with Check All/Uncheck All.

Repairing an "in use" Table

The multi-table mode is the only method (unless we know the exact SQL query to type) for repairing a corrupted table. Such tables may be shown with the in use flag in the database list. Users seeking help in the support forums for phpMyAdmin often receive this tip from experienced phpMyAdmin users.

Database Operations

The Operations tab in the Database view gives access to a panel that enables us to perform operations on a database taken as a whole.

Renaming a Database

Starting with phpMyAdmin 2.6.0, a Rename database dialog is available. Although this operation is not directly supported by MySQL, phpMyAdmin does it indirectly by creating a new database, renaming each table (thus sending it to the new database), and dropping the original database.

Copying a Database

Since phpMyAdmin 2.6.1, it is possible to do a complete copy of a database, even if MySQL itself does not support this operation natively.

Summary

In this article, we covered the operations we can perform on whole tables or databases. We also took a look at table maintenance operations for table repair and optimization, changing various table attributes, table movements, including renaming and moving to another database, and multi-table operations.

About the Author

Marc Delisle is a member of the MySQL Developers Guild—which regroups community developers—because of his involvement with phpMyAdmin. He started to contribute to this popular MySQL web interface in December 1998, when he made the first multi-language version. He has been actively involved with this software project since May 2001 as a developer and project administrator. Marc has worked since 1980 at Collège de Sherbrooke, Québec, Canada, as an application programmer and network manager. He has also been teaching networking, security, and PHP/MySQL application development. In one of his classes, he was pleased to meet a phpMyAdmin user from Argentina. Marc lives in Sherbrooke with his wife and they enjoy spending time with their four children.