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 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 will display 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 USAGE ON *.* TO 'joeuser'@'localhost' IDENTIFIED BY  |
| PASSWORD '34f3a6996d856efd'                                |
| GRANT ALL PRIVILEGES ON 'testDB'.* TO 'joeuser'@'localhost'|
| WITH GRANT OPTION                                          |
+------------------------------------------------------------+
2 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.

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 will 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;
+--------------------------+--------------------+
| 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 chapter 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 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')                           
                      ) 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 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 29.1.

Table 29.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.
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. The columns returned from this command are described in Table 29.2.

Table 29.2. Columns in the SHOW TABLE STATUS Result

Column Name	Description
Name	The name of the table.
Type	The table type: MyISAM, BDB, InnoDB, MERGE or MEMORY.
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).
Create_options	Any extra options used in the CREATE TABLE statement.
Comment	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 VARIABLESnothing 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 the number of allowable simultaneous connections could be set too low for the flood of traffic to your site.

• 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 slow query, 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 http://dev.mysql.com/doc/S/H/SHOW_STATUS.html.

The SHOW VARIABLES command produces even more results than SHOW STATUSmore than 80! 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 http://dev.mysql.com/doc/S/H/SHOW_VARIABLES.html. After you know the values you have, you can change them in your MySQL configuration file or startup command.