7.20 SHOW syntax (Get information about tables, columns,...)

   SHOW DATABASES [LIKE wild]
or SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW STATUS
or SHOW VARIABLES [LIKE wild]
or SHOW PROCESSLIST
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW GRANTS FOR user

SHOW provides information about databases, tables, columns or the server. If the LIKE wild part is used, the wild string can be a string that uses the SQL `%' and `_' wildcard characters.

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. These two statements are equivalent: 

mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;

SHOW DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command.

SHOW TABLES lists the tables in a given database. You can also get this list using the mysqlshow db_name command.

Note: If a user doesn't have any privileges for a table, the table will not show up in the output from SHOW TABLES or mysqlshow db_name.

SHOW COLUMNS lists the columns in a given table. If the column types are different than you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. 7.6.1 Silent column specification changes.

The DESCRIBE statement provides information similar to SHOW COLUMNS. DESCRIBE.

SHOW TABLE STATUS (new in version 3.23) works likes SHOW STATUS, but provides a lot of information about each table. You can also get this list using the mysqlshow --status db_name command. The following columns are returned:

Column Meaning
Name Name of the table
Type Type of table (NISAM, MyISAM or HEAP)
Row_format The row storage format (Fixed, Dynamic, or Compressed)
Rows Number of rows
Avg_row_length Average row length
Data_length Length of the data file
Max_data_length Max length of the data file
Index_length Length of the index file
Data_free Number of allocated but not used bytes
Auto_increment Next autoincrement value
Create_time When the table was created
Update_time When the data file was last updated
Check_time When one last run a check on the table
Create_options Extra options used with CREATE TABLE
Comment The comment used when creating the table (or some information why MySQL couldn't access the table information).

SHOW FIELDS is a synonym for SHOW COLUMNS and SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's columns or indexes with mysqlshow db_name tbl_name or mysqlshow -k db_name tbl_name.

SHOW INDEX returns the index information in a format that closely resembles the SQLStatistics call in ODBC. The following columns are returned:

Column Meaning
Table Name of the table
Non_unique 0 if the index can't contain duplicates.
Key_name Name of the index
Seq_in_index Column sequence number in index, starting with 1.
Column_name Column name.
Collation How the column is sorted in the index. In MySQL, this can have values A (Ascending) or NULL (Not sorted).
Cardinality Number of unique values in the index. This is updated by running isamchk -a.
Sub_part Number of indexed characters if the column is only partly indexed. NULL if the entire key is indexed.

SHOW STATUS provides server status information (like mysqladmin extended-status). The output resembles that shown below, though the format and numbers may differ somewhat: 

+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_clients          | 0      |
| Aborted_connects         | 0      |
| Connections              | 17     |
| Created_tmp_tables       | 0      |
| Delayed_insert_threads   | 0      |
| Delayed_writes           | 0      |
| Delayed_errors           | 0      |
| Flush_commands           | 2      |
| Handler_delete           | 2      |
| Handler_read_first       | 0      |
| Handler_read_key         | 1      |
| Handler_read_next        | 0      |
| Handler_read_rnd         | 35     |
| Handler_update           | 0      |
| Handler_write            | 2      |
| Key_blocks_used          | 0      |
| Key_read_requests        | 0      |
| Key_reads                | 0      |
| Key_write_requests       | 0      |
| Key_writes               | 0      |
| Max_used_connections     | 1      |
| Not_flushed_key_blocks   | 0      |
| Not_flushed_delayed_rows | 0      |
| Open_tables              | 1      |
| Open_files               | 2      |
| Open_streams             | 0      |
| Opened_tables            | 11     |
| Questions                | 14     |
| Slow_queries             | 0      |
| Threads_connected        | 1      |
| Threads_running          | 1      |
| Uptime                   | 149111 |
+--------------------------+--------+

The status variables listed above have the following meaning:

Aborted_clients Number of connections that has been aborted because the client has died without closing the connection properly.
Aborted_connects Number of tries to connect to the MySQL server that has failed.
Connections Number of connection attempts to the MySQL server.
Created_tmp_tables Number of implicit temporary tables that has been created while executing statements.
Delayed_insert_threads Number of delayed insert handler threads in use.
Delayed_writes Number of rows written with INSERT DELAYED.
Delayed_errors Number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key).
Flush_commands Number of executed FLUSH commands.
Handler_delete Number of requests to delete a row from a table.
Handler_read_first Number of requests to read the first row in a table.
Handler_read_key Number of requests to read a row based on a key.
Handler_read_next Number of requests to read next row in key order.
Handler_read_rnd Number of requests to read a row based on a fixed position.
Handler_update Number of requests to update a row in a table.
Handler_write Number of requests to insert a row in a table.
Key_blocks_used The number of used blocks in the key cache.
Key_read_requests The number of requests to read a key block from the cache.
Key_reads The number of physical reads of a key block from disk.
Key_write_requests The number of requests to write a key block to the cache.
Key_writes The number of physical writes of a key block to disk.
Max_used_connections The maximum number of connections that has been in use simultaneously.
Not_flushed_key_blocks Keys blocks in the key cache that has changed but hasn't yet been flushed to disk.
Not_flushed_delayed_rows Number of rows waiting to be written in INSERT DELAY queues.
Open_tables Number of tables that are open.
Open_files Number of files that are open.
Open_streams Number of streams that are open (used mainly for logging)
Opened_tables Number of tables that has been opened.
Questions Number of queries sent to the server.
Slow_queries Number of queries that has taken more than long_query_time
Threads_connected Number of currently open connections.
Threads_running Number of threads that are not sleeping.
Uptime How many seconds the server has been up.

Some comments about the above:

  • If Opened_tables is big, then your table_cache variable is probably too small.
  • If key_reads is big, then your key_cache is probably too small. The cache hit rate can be calculated with key_reads/key_read_requests.
  • If Handler_read_rnd is big, then you have a probably a lot of queries that requires MySQL to scan whole tables or you have joins that doesn't use keys properly.

SHOW VARIABLES shows the values of the some of MySQL system variables. You can also get this information using the mysqladmin variables command. If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts up. The output resembles that shown below, though the format and numbers may differ somewhat: 

+------------------------+--------------------------+
| Variable_name          | Value                    |
+------------------------+--------------------------+
| back_log               | 5                        |
| connect_timeout        | 5                        |
| basedir                | /my/monty/               |
| datadir                | /my/monty/data/          |
| delayed_insert_limit   | 100                      |
| delayed_insert_timeout | 300                      |
| delayed_queue_size     | 1000                     |
| join_buffer_size       | 131072                   |
| flush_time             | 0                        |
| key_buffer_size        | 1048540                  |
| language               | /my/monty/share/english/ |
| log                    | OFF                      |
| log_update             | OFF                      |
| long_query_time        | 10                       |
| low_priority_updates   | OFF                      |
| max_allowed_packet     | 1048576                  |
| max_connections        | 100                      |
| max_connect_errors     | 10                       |
| max_delayed_threads    | 20                       |
| max_heap_table_size    | 16777216                 |
| max_join_size          | 4294967295               |
| max_sort_length        | 1024                     |
| max_tmp_tables         | 32                       |
| net_buffer_length      | 16384                    |
| port                   | 3306                     |
| protocol-version       | 10                       |
| record_buffer          | 131072                   |
| skip_locking           | ON                       |
| socket                 | /tmp/mysql.sock          |
| sort_buffer            | 2097116                  |
| table_cache            | 64                       |
| thread_stack           | 131072                   |
| tmp_table_size         | 1048576                  |
| tmpdir                 | /machine/tmp/            |
| version                | 3.23.0-alpha-debug       |
| wait_timeout           | 28800                    |
+------------------------+--------------------------+

Server parameters.

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the process privilege, you can see all threads. Otherwise, you can see only your own threads. KILL.

SHOW GRANTS FOR user lists the grant commands that must be issued to duplicate the grants for a user. 

mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root''localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+