Using the SHOW Command

Using the `SHOW` Command

There are several different uses of the SHOW command, which produces 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 GRANTS FOR user SHOW DATABASES [LIKE something] SHOW [OPEN] TABLES [FROM database_name] [LIKE something] SHOW CREATE TABLE table_name 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] SHOW VARIABLES [LIKE something]

The SHOW GRANTS command displays the privileges for a given user at a given host. This is an 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 joeuser@localhost; +----------------------------------------------------------------+ | Grants for joeuser@localhost                                   | +----------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'joeuser'@'localhost' IDENTIFIED| | BY  PASSWORD ' *13883BDDBE566ECEFF0501CDE9B293303116521A'      | +----------------------------------------------------------------+ 1 rows in set (0.00 sec)

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 see only the information relevant to yourself. 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 chapter. If you are not the root-level user, some of these commands will not be available to you or will display only limited information.

Some popular SHOW commands follow; for more information, see the MySQL Manual at http://dev.mysql.com/doc/refman/5.0/en/show.html.

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 thatit lists all the databases on your 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              | | testTable           | +---------------------+ 5 rows in set (0.01 sec)

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

mysql> SHOW OPEN TABLES; +----------+-------------------+--------+-------------+ | Database | Table             | In_use | Name_locked | +----------+-------------------+--------+-------------+ | mysql    | procs_priv        | 0      | 0           | | mysql    | db                | 0      | 0           | | mysql    | host              | 0      | 0           | | testdb   | grocery_inventory | 0      | 0           | | mysql    | user              | 0      | 0           | | mysql    | tables_priv       | 0      | 0           | | mysql    | columns_priv      | 0      | 0           | +----------+-------------------+--------+-------------+ 7 rows in set (0.00 sec)

Using this information in conjunction with the FLUSH TABLES command you learned earlier in this chapter helps 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 helpful command is SHOW CREATE TABLE, which does what it sounds likeit 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')                       ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +--------------------+---------------------------------------------+ 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)     | NO   | PRI |         | auto_increment | | item_name  | varchar(50) | NO   |     |         |                | | item_desc  | text        | YES  |     |         |                | | item_price | float       | NO   |     |         |                | | curr_qty   | int(11)     | NO   |     |         |                | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)

By the Way

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

The SHOW INDEX command displays 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 the cardinality of the index. Table 31.1 describes the columns returned from this command.

Table 31.1. Columns in the `SHOW INDEX` Result
Column Name	Description
`Table`	The name of the table.
`Non_unique`	1 or 0.
	1 = index can contain duplicates.
	0 = index can't contain duplicates.
`Key_name`	The name of the index.
`Seq_in_index`	The column sequence number for the Index; starts at 1.
`Column_name`	The name of the column.
`Collation`	The sort order of the column, either `A` (ascending) or `NULL` (not sorted).
`Cardinality`	Number of unique values in the index.
`Sub_part`	On a partially indexed column, this shows the number of indexed characters, or `NULL` if the entire key is indexed.
`Packed`	The size of numeric columns.
`Null`	Displays whether the column can contain `NULL` values.
`Index_type`	The index method used.
`Comment`	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. Table 31.2 describes the columns returned from this command.

Table 31.2. Columns in the `SHOW TABLE STATUS` Result
Column Name	Description
`Name`	The name of the table.
`Engine`	`The storage engine used for this table`.
`Version`	`The version of the table's *.frm file`.
`Row_format`	The row storage format: fixed, dynamic, or compressed.
`Rows`	The number of rows.
`Avg_row_length`	The average row length.
`Data_length`	The length of the data file.
`Max_data_length`	The maximum length of the data file.
`Index_length`	The length of the index file.
`Data_free`	The number of bytes allocated but not used.
`Auto_increment`	The next value to be used in an `auto_increment` field.
`Create_time`	The date and time when the table was created (in `datetime` format).
`Update_time`	The date and time when the data file was last updated (in `datetime` format).
`Check_time`	The date and time when the table was last checked (in `datetime` format).
Collation	The character set and collation type for the table.
`Checksum`	The checksum value of the table, if applicable.
`Create_options`	Any extra options used in the `CREATE TABLE` statement.
`Comment`	Any comments added when the table was created. Additionally, InnoDB tables uses this column to report the free space in the `tablespace`.

Retrieving System Status

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

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

Aborted_connectsThe number of failed attempts to connect to the MySQL server. Any time you see an aborted connection, you should investigate the problem. It could be related to a bad username and password in a script, or the number of allowable simultaneous connections could be set too low for the flood of traffic to your site.
ConnectionsThe aggregate number of connection attempts to the MySQL server during the current period of uptime.
Max_used_connectionsThe maximum number of connections that have been in use simultaneously during the current period of uptime.
Slow_queriesThe number of queries that have taken more than long_query_time, which defaults to 10 seconds. If you have more than one slow query, it's time to investigate your SQL syntax!
UptimeTotal 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 http://dev.mysql.com/doc/refman/5.0/en/show-status.html.

The SHOW VARIABLES command produces 212 results, which control the general operation of MySQL and include the following useful tidbits:

connect_timeoutShows the number of seconds the MySQL server will wait during a connection attempt before it gives up
max_connectionsThe allowable number of simultaneous connections to MySQL before a connection is refused
portThe port on which MySQL is running
table_typeThe table type for MySQL
versionThe 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 http://dev.mysql.com/doc/refman/5.0/en/show-variables.html. After you know the values you have, you can change them in your MySQL configuration file or startup command.