Using the 'SHOW' Command

There are several different uses of the SHOW command, which will produce output displaying a great deal of useful information about your MySQL database, users, and tables. Depending on your access level, some of the SHOW commands will not be available to you or will provide only minimal information. The root-level user has the capability to use all the SHOW commands, with the most comprehensive results.

The common uses of SHOW include the following, which you'll soon learn about in more detail:

SHOW [OPEN] TABLES [FROM database_name] [LIKE something]
SHOW [FULL] COLUMNS FROM table_name [FROM database_name] [LIKE something]
SHOW INDEX FROM table_name [FROM database_name]
SHOW TABLE STATUS [FROM db_name] [LIKE something]
SHOW STATUS [LIKE something]

The SHOW GRANTS command will display the privileges for a given user at a given host. This is any easy way to check on the current status of a user, especially if you have a request to modify a user's privileges. With SHOW GRANTS, you can check first to see that the user doesn't already have the requested privileges. For example, see the privileges available to the joeuser user:

mysql> show grants for joe@localhost;
| Grants for joeuser@localhost                              |
| GRANT USAGE ON *.* TO 'joeuser'@'localhost' \             
IDENTIFIED BY PASSWORD '34f3a6996d856efd'                   |
| GRANT ALL PRIVILEGES ON testDB.* TO 'joeuser'@'localhost' |

If you're not the root-level user or the joeuser user, you'll get an error. Unless you're the root-level user, you can only see the information relevant to your user. For example, the joeuser user isn't allowed to view information about the root-level user:

mysql> show grants for root@localhost;
ERROR 1044: Access denied for user:'joeuser@localhost' to database 'mysql'

Be aware of your privilege level throughout the remainder of this hour. If you are not the root-level user, some of these commands will not be available to you or will display only limited information.

Retrieving Information About Databases and Tables

You've used a few of the basic SHOW commands earlier in this book to view the list of databases and tables on your MySQL server. As a refresher, the SHOW DATABASES command does just that?it lists all the databases on the MySQL server:

mysql> show databases;
| Database          |
| testDB            |
| mysql             |
2 rows in set (0.00 sec)

After you've selected a database to work with, you can also use SHOW to list the tables in the database. In this example, we're using testDB (your table listing may vary):

mysql> show tables;
| Tables_in_testDB     |
| grocery_inventory    |
| email                |
| master_name          |
| myTest               |
| testable             |
5 rows in set (0.01 sec)

If you add OPEN to your SHOW TABLES command, you will get a list of all the tables in the table cache, showing how many times they're cached and in use:

| Open_tables_in_testDB | Comment            |
| grocery_inventory     | cached=1, in_use=0 |
| email                 | cached=1, in_use=0 |
| testTable             | cached=1, in_use=0 |
| master_name           | cached=1, in_use=0 |
| myTest                | cached=1, in_use=0 |
5 rows in set (0.00 sec)

Using this information in conjunction with the FLUSH TABLES command you learned earlier in this hour will help keep your database running smoothly. If SHOW OPEN TABLES shows that tables are cached numerous times, but aren't currently in use, go ahead and use FLUSH TABLES to free up that memory.

Retrieving Table Structure Information

A very helpful command is SHOW CREATE TABLE, which does what it sounds like?it shows you the SQL statement used to create a specified table:

mysql> show create table grocery_inventory;
| Table             | Create Table
| grocery_inventory | CREATE TABLE 'grocery_inventory' (
                      'id' int(11) NOT NULL auto_increment,
                           'item_name' varchar(50) NOT NULL default '',
                      'item_desc' text,
                      'item_price' float NOT NULL default '0',
                      'curr_qty' int(11) NOT NULL default '0',
                      PRIMARY KEY ('id')
                           ) TYPE=MyISAM
1 row in set (0.00 sec)

This is essentially the same information you'd get if you dumped the table schema, but the SHOW CREATE TABLE command can be used quickly if you're just looking for a reminder or a simple reference to a particular table-creation statement.

If you need to know the structure of the table, but don't necessarily need the SQL command to create it, you can use the SHOW COLUMNS command:

mysql> show columns from grocery_inventory;
| Field      | Type       | Null | Key  | Default | Extra          |
| id         | int(11)    |      | PRI  | NULL    | auto_increment |
| item_name  | varchar(50)|      |      |         |                |
| item_desc  | text       | YES  |      | NULL    |                |
| item_price | float      |      |      | 0       |                |
| curr_qty   | int(11)    |      |      | 0       |                |
5 rows in set (0.00 sec)


The SHOW COLUMNS and DESCRIBE commands are aliases for one another and, therefore, do the same thing.

The SHOW INDEX command will display information about all the indexes present in a particular table. The syntax is

SHOW INDEX FROM table_name [FROM database_name]

This command produces a table full of information, ranging from the column name to cardinality of the index. The columns returned from this command are described in Table 24.1.

Table 24.1. Columns in the SHOW INDEX Result

Column Name



The name of the table.


1 or 0.

1 = index can contain duplicates.

0 = index can't contain duplicates.


The name of the index.


The column sequence number for the

Index; starts at 1.


The name of the column.


The sort order of the column, either A (ascending) or NULL (not sorted).


Number of unique values in the index.


On a partially-indexed column, this shows the number of indexed characters, or NULL if the entire key is indexed.


The size of numeric columns.


Any additional comments.

Another command that produces a wide table full of results is the SHOW TABLE STATUS command. The syntax of this command is

SHOW TABLE STATUS [FROM database_name] LIKE 'something'

This command produces a table full of information, ranging from the size and number of rows to the next value to be used in an auto_increment field. The columns returned from this command are described in Table 24.2.

Table 24.2. Columns in the SHOW TABLE STATUS Result

Column Name



The name of the table.


The table type: MyISAM, BDB, InnoDB, or Gemini.


The row storage format: fixed, dynamic, or compressed.


The number of rows.


The average row length.


The length of the data file.


The maximum length of the data file.


The length of the index file.


The number of bytes allocated but not used.


The next value to be used in an auto_increment field.


The date and time when the table was created (in datetime format).


The date and time of when the data file was last updated (in datetime format).


The date and time of when the table was last checked (in datetime format).


Any extra options used in the CREATE TABLE statement.


Any comments added when the table was created. Additionally, InnoDB tables will use this column to report the free space in the tablespace.

Retrieving System Status

The SHOW STATUS and SHOW VARIABLES commands will quickly provide important information about your database server. The syntax for these commands is simply SHOW STATUS or SHOW VARIABLES, nothing fancy.

There are no less than 54 status variables as the output of SHOW STATUS, but the most useful are

  • Aborted_connects? The number of failed attempts to connect to the MySQL server. Anytime you see an aborted connection, you should investigate the problem. It could be related to a bad username and password in a script, or your number of simultaneous connections could be set too low.

  • Connections? The aggregate number of connection attempts to the MySQL server during the current period of uptime.

  • Max_used_connections? The maximum number of connections that have been in use simultaneously during the current period of uptime.

  • Slow_queries? The number of queries that have taken more than long_query_time, which defaults to 10 seconds. If you have more than one, it's time to investigate your SQL syntax!

  • Uptime? Total number of seconds the server has been up during the current period of uptime.

You can find a comprehensive list of SHOW STATUS variables and an explanation of their values in the MySQL manual, located at

The SHOW VARIABLES command produces even more results than SHOW STATUS?approximately 82! The variables reported from SHOW VARIABLES control the general operation of MySQL and include the following useful tidbits:

  • connect_timeout? Shows the number of seconds the MySQL server will wait during a connection attempt before it gives up.

  • have_innodb? Will show YES if MySQL supports InnoDB tables.

  • have_bdb? Will show YES if MySQL supports Berkeley DB tables.

  • max_connections? The allowable number of simultaneous connections to MySQL before a connection is refused.

  • port? The port on which MySQL is running.

  • table_type? The default table type for MySQL, usually MyISAM.

  • version? The MySQL version number.

You can find a comprehensive list of the variables returned by the SHOW VARIABLES results and an explanation of their values in the MySQL manual at After you know the values you have, you can change them in your MySQL configuration file or startup command.

    Part III: Getting Involved with the Code