Chapter 17. Catalogs

Table of Contents

17.1. Introduction
17.2. Schema Tables
17.2.1. Table Status
17.2.2. Row Status
17.3. Schema Indexes

17.1. Introduction

This section deals with viewing catalogs, schemata, tables and their properties, creating databases, and performing a number of operations on tables. Existing catalogs and schemata are listed in the lower left area of the sidebar.

If there are many schemata on your MySQL server, the field with the magnifying glass icon may be handy for filtering the schemata you are looking for. Typing in t or T, for example, will set the filter to schemata whose names begin with a t. The filter works in a case-insensitive fashion.

You may also filter schemata using the wild card ? to stand in for any single character and * for zero to any number of characters. For example the string *boo?* will find schemata with the names books, coursebooks and bookings.

Select the database that you wish to view. Right click on any database and choose the Refresh Schemata List option to refresh the database list. Clicking on a database will cause the Schema Tables and Schema Indices tabs to be populated with the tables and indexes of the selected database.

To create a new database, right click on an existing database and choose the Create New Schema option.

17.2. Schema Tables

The Schema Tables tab lists the different tables in the selected database, along with the rowcount, data length, and index length for those tables. For MyISAM tables, you also find the date and time when the table was last modified, under Update Time. You can update the list of tables by pressing the Refresh button.

The tables are ordered by table name initially, but you may change that sorting by clicking on the appropriate headings (Type, Row Format, etc.).

To drop a table, right click on the table and choose the Drop Table option from the pop-up menu. To create a table you can either click the Create Table button at the bottom of the window, or right-click on a table and choose the Create Table option from the pop-up menu.

To edit a table's columns and indexes, right-click on the desired table and choose the Edit Table option from the pop-up menu. This will launch the MySQL Table Editor, which you can use to modify the table. For more information see Chapter 18, The MySQL Table Editor .

To edit a table's actual data, right click on the table and choose the Edit Table Data option. This will launch the MySQL Query Browser and load the table's data into the result area of the MySQL Query Browser.

To perform operations on multiple tables select more than one table. For the selected tables, you can perform operations by either clicking the Maintenance button at the bottom of the table list or by right clicking on one of the selected tables and choosing from the Maintenance sub-menu of the pop-up menu:

The available options are:

  • Optimize Table: This corresponds to the OPTIMIZE TABLE SQL command and should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use this command to reclaim the unused space and to defragment the datafile. Note that table optimization works for MyISAM and BDB tables only. For more information see OPTIMIZE TABLE Syntax.

  • Check Table: This corresponds to the CHECK TABLE SQL command and is used to diagnose table problems. For more information see CHECK TABLE Syntax.

  • Repair Table: This corresponds to the REPAIR TABLE SQL command and should be used in case of table problems. Note that this command works for MyISAM tables only. For more information see REPAIR TABLE Syntax.

Clicking on a table name will display its properties in the details area when the details area is active. To display the details area, click the Details >> button.

17.2.1. Table Status

In this tab, you find detailed information about the selected table. This information could also be retrieved by issuing a SHOW TABLE STATUS LIKE 'tbl' SQL command. For more information see SHOW TABLE STATUS Syntax.

17.2.2. Row Status

In this tab, you find detailed information about the rows of the selected table. This information can also be retrieved by issuing a SHOW TABLE STATUS LIKE 'tbl' SQL command. For more information see SHOW TABLE STATUS Syntax.

17.3. Schema Indexes

The Schema Indices tab lists the indexes of the selected database. Besides the index names and the table the index belongs to, you can also see the index type, whether values are unique, and whether NULL values are allowed. You can also get this information by issuing a SHOW INDEX SQL command in a command-line client (see SHOW DATABASES Syntax). For more information about indexes, see Column Indexes.

To see the columns that form a given index, double click on the index.