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

Exclusive offer: get 50% off this eBook here
Mastering phpMyAdmin 3.3.x for Effective MySQL Management

Mastering phpMyAdmin 3.3.x for Effective MySQL Management — Save 50%

A complete guide to get started with phpMyAdmin 3.3 and master its features

£14.99    £7.50
by Marc Delisle | October 2010 | MySQL Open Source PHP

In this article, by Marc Delisle, author of Mastering phpMyAdmin 3.3.x for Effective MySQL Management, 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.

This article specifically covers:

  • Maintaining a table
  • Changing table attributes
  • Renaming, moving, and copying tables
  • Multi-table operations
  • Database operations

 

Mastering 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!
        Read more about this book      

(For more resources on PHP, see here.)

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
Mastering phpMyAdmin 3.3.x for Effective MySQL Management A complete guide to get started with phpMyAdmin 3.3 and master its features
Published: October 2010
eBook Price: £14.99
Book Price: £24.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on PHP, see here.)

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 on 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 we use an ORDER BY clause later on, and the table is already physically sorted on this field, the performance should be better.

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

After the sort has been performed 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 as Ascending or Descending.

If we insert another row, describing a new book from author 1, and then click on Browse, the book will not be displayed along with the other books for this author because the sort was done before the insertion.

Table collation

To better see the issues of collation change at various levels, let's first create a new author with a character é in his name:

The name column currently has a latin1_swedish_ci collation, as can be seen via the Structure page. On the Operations page, if we change the collation for table author from latin1_swedish_ci to, say, utf8_general_ci, this generates:

ALTER TABLE `author` DEFAULT CHARACTER SET utf8 COLLATE
utf8_general_ci

Therefore, we only changed the default collation for future columns that will be added to this table.

In an effort to further test collation changes, we can go to the Structure page and change the collation of the author's name column to utf8_general_ci (by clicking on the Change icon). Having done that, we browse the author table and still see the é character, because MySQL updated the contents of this column on collation change.

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. The table can be read faster but will take more time to update. It's available for the MyISAM storage engine.
  • CHECKSUM: This option makes MySQL compute a checksum for each row. This results in slower updates, but finding corrupted tables becomes easier. It's available for MyISAM only.
  • DELAY_KEY_WRITE: This option instructs MySQL not to write the index updates immediately, but to queue them for writing later. This improves performance but there is a negative trade-off: the index might need to be rebuilt in case of a server failure (see http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-optimization-tips.html). It's available for MyISAM only.
  • ROW_FORMAT: To the storage engines that support this feature (MyISAM, InnoDB, PBXT, and Maria), a choice of row format is presented. The default value being the current state of this table's row format.
  • TRANSACTIONAL, PAGE_CHECKSUM: This option applies to the Maria storage engine, which will be renamed Aria in a future version. The TRANSACTIONAL option marks this table as being transactional; however, the exact meaning of this option varies, as future versions of this storage engine will gain more transactional features. PAGE_CHECKSUM computes a checksum on all index pages. This is currently documented at http://askmonty.org/wiki/Manual:Maria_storage_engine
  • auto-increment: This option changes the auto-increment value. It's shown only if the table's primary key has the auto-increment attribute.

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 screenshot) manipulates a table in two ways— it changes the table's 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. This could take a long time depending on the table's size.

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 remain in the Table view for the book table, unless we selected Switch to copied table in which case we are moved to the Table view of the newly-created table.

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

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 and choosing Data only.

For example, book data is coming from various sources (various publishers) in the form of one table per publisher and we want to aggregate all of the data in one place. For MyISAM, a similar result can be obtained by using the Merge storage engine (which is a collection of identical MyISAM tables). However, if the table is InnoDB, we need to rely on phpMyAdmin's Copy feature.

Mastering phpMyAdmin 3.3.x for Effective MySQL Management A complete guide to get started with phpMyAdmin 3.3 and master its features
Published: October 2010
eBook Price: £14.99
Book Price: £24.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on PHP, see here.)

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 book-copy and book tables, and choose the Check table operation for the selected 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

A Rename database to 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

It's also possible to make a complete copy of a database, even if MySQL itself does not support this operation natively. The options are similar to those already explained for the table copy.

Summary

This article covered the operations we can perform on entire tables or databases. It also looked 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.


Further resources on this subject:


About the Author :


Marc Delisle

Marc Delisle was awarded "MySQL Community Member of the year 2009" because of his involvement with phpMyAdmin. He started to contribute to the project in December 1998, when he made the multi-language version. He is still involved with phpMyAdmin as a developer and project administrator.

Marc is a system administrator at Cegep de Sherbrooke, Québec, Canada. He has been teaching networking, security, and web 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.

Books From Packt


Expert PHP 5 Tools
Expert PHP 5 Tools

MySQL 5.1 Plugin Development
MySQL 5.1 Plugin Development

CMS Design Using PHP and jQuery
CMS Design Using PHP and jQuery

MySQL Admin Cookbook
MySQL Admin Cookbook

Drupal 6 Panels Cookbook
Drupal 6 Panels Cookbook

High Availability MySQL Cookbook
High Availability MySQL Cookbook

Drupal 7
Drupal 7

PHP 5 E-commerce Development
PHP 5 E-commerce Development


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software