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 [LIKE wild] or SHOW VARIABLES [LIKE wild] or SHOW [FULL] 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. See section 7.7.1 Silent column specification changes.
The DESCRIBE statement provides information similar to SHOW COLUMNS. See section 7.25 DESCRIBE syntax (Get information about columns).
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 (BDB, ISAM, 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_launch_threads | 0 | | 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. |
Bytes_received | Number of bytes received from the client |
Bytes_sent | Number of bytes received from the client |
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 times a row was deleted from a table. |
Handler_read_first | Number of times the first entry was read from an index. If this is high, it suggests that the server is doing a lot of full index scans, for example, SELECT col1 FROM foo, assuming that col1 is indexed |
Handler_read_key | Number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed. |
Handler_read_next | Number of requests to read next row in key order. This will be incremented if you are querying an index column with a range contraint. This also will be incremented if you are doing an index scan. |
Handler_read_rnd | Number of requests to read a row based on a fixed position. This will be high if you are doing a lot of queries that require sorting of the result. |
Handler_read_rnd_next | Number of requests to read the next row in the datafile. This will be high if you are doing a lot of table scans - generally this suggests that you tables are not properly indexed or that you queries are not written to take advantaged of the indeces you have.. |
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_launch_threads | Number of threads that has taken more than slow_launch_time to connect. |
Slow_queries | Number of queries that has taken more than long_query_time |
Threads_cached | Number of threads in the thread cache. |
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:
Opened_tables is big, then your table_cache variable is probably too small. 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. 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 | | interactive_timeout | 28800 | | 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 | | slow_launch_time | 2 | | 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 | +------------------------+--------------------------+
See section 12.2.3 Tuning 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. See section 7.22 KILL syntax. If you don't use the the FULL option, then only the first 100 characters of each query will be shown.
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 | +---------------------------------------------------------------------+