Information Commands

A database system can be monitored, and statistics about it collected, using a small set of special commands and built-in databases, which can then inform optimizations. The server can also be queried on how to use a specific command.

HELP

Comments within an SQL statement are written as # to the end of the line, -- (followed by whitespace) to the end of the line, or /* ... */, which can span multiple lines.

CREATE TABLE tbl(
  a INT, # this is a comment
  b INT, -- this is another comment
  c INT /* this is a multiline
    comment */
);

The three comment syntaxes MySQL supports.

To obtain information from the MySQL Reference Manual, use HELP 'search_string', where search_string can contain the wildcard characters % and _ as with the LIKE operator.

HELP 'contents';
HELP 'Data Types';
HELP 'REPLACE';

Retrieving the contents page, a topic, and a specific command from the built-in help system.

-- HELP: querying the built-in MySQL Reference Manual
HELP 'contents';       -- the contents page
HELP 'Data Types';     -- a topic
HELP 'REPLACE';        -- a command

-- HELP: querying the built-in MySQL Reference Manual
HELP 'contents';       -- the contents page
HELP 'Data Types';     -- a topic
HELP 'REPLACE';        -- a command

EXPLAIN, DESCRIBE, DESC

EXPLAIN, DESCRIBE, and DESC obtain information about a specific table or statement; the three keywords are synonymous. See also EXPLAIN and Execution Plans and Query Optimizer.

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type:
    EXTENDED | PARTITIONS | FORMAT = format_name

format_name:
    TRADITIONAL | JSON

explainable_stmt:
    SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement

The full EXPLAIN/DESCRIBE/DESC syntax.

EXPLAIN tbl;
EXPLAIN SELECT * FROM tbl;

DESCRIBE-style column listing versus a query execution plan – both spellings use the same EXPLAIN keyword, disambiguated by what follows it.

CREATE TABLE tbl(
    a INT PRIMARY KEY,
    b INT DEFAULT 5,
    c INT AUTO_INCREMENT UNIQUE
);

-- DESCRIBE-style: lists the table's columns
EXPLAIN tbl;

-- Query-plan style: EXPLAIN before a SELECT
EXPLAIN SELECT * FROM tbl;

CREATE TABLE tbl(
    a INT PRIMARY KEY,
    b INT DEFAULT 5,
    c INT AUTO_INCREMENT UNIQUE
);

-- DESCRIBE-style: lists the table's columns
EXPLAIN tbl;

-- Query-plan style: EXPLAIN before a SELECT
EXPLAIN SELECT * FROM tbl;

Field	Type	Null	Key	Default	Extra
a	int	NO	PRI	NULL
b	int	YES		5
c	int	NO	UNI	NULL	auto_increment

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tbl	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL

The system variable explain_format determines the output format of an EXPLAIN statement when no FORMAT option is given. For example, if explain_format is TREE, output uses the tree-like format, as if FORMAT=TREE had been specified.

EXPLAIN ANALYZE runs a statement and produces EXPLAIN output along with timing and additional, iterator-based information about how the optimizer's expectations matched the actual execution. For each iterator, it reports:

Estimated execution cost (some iterators are not accounted for by the cost model and are excluded from the estimate).
Estimated number of returned rows.
Time to return the first row.
Time spent executing this iterator, including child iterators but not parent iterators, in milliseconds (averaged per loop when there are multiple loops).
Number of rows returned by the iterator.
Number of loops.

EXPLAIN ANALYZE SELECT * FROM Students JOIN Tests;

Runs the query and reports actual timing/row-count data alongside the plan.

SHOW

SHOW has many forms that provide information about databases, tables, columns, or status information about the server.

SHOW BINARY LOGS
SHOW MASTER LOGS

Lists the log files on the server and their sizes.

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If log_name is omitted, the first binary log is used.

SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr]

Shows all available character sets.

SHOW COLLATION [LIKE 'pattern' | WHERE expr]

Lists collations supported by the server.

SHOW [FULL] COLUMNS {FROM|IN} tbl [{FROM|IN} db] [LIKE 'pattern' | WHERE expr]

Displays information about the columns in a table.

SHOW CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db

Shows the statement that creates the database.

SHOW CREATE EVENT event
SHOW CREATE FUNCTION function
SHOW CREATE PROCEDURE procedure
SHOW CREATE TABLE tbl
SHOW CREATE TRIGGER trigger
SHOW CREATE VIEW view

Displays the statement that creates the named object.

SHOW {DATABASES|SCHEMAS} [LIKE 'pattern' | WHERE expr]

Lists the databases on the server, including built-in ones.

SHOW ENGINE engine {STATUS|MUTEX}
SHOW [STORAGE] ENGINES

Operational information about one storage engine, or a list of the server's storage engines. See also Storage Engines.

SHOW ENGINES;

SHOW ENGINES;

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

Errors resulting from the last statement executed in the current session.

SHOW EVENTS [{FROM|IN} schema] [LIKE 'pattern' | WHERE expr]

Displays information about Event Manager events.

SHOW FUNCTION CODE function
SHOW FUNCTION STATUS [LIKE 'pattern' | WHERE expr]
SHOW PROCEDURE CODE procedure
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr]

Internal implementation and characteristics of a stored function or procedure. See also Compound Statements.

SHOW GRANTS [FOR user]

Lists the statement that reproduces the privileges granted to an account. See also Privileges.

SHOW {INDEX|INDEXES|KEYS} {FROM|IN} tbl [{FROM|IN} db] [WHERE expr]

Displays information about the indexes in a table. See also Indexes.

SHOW MASTER STATUS

Status information about the binary log files of the source/master.

SHOW OPEN TABLES [{FROM|IN} db] [LIKE 'pattern' | WHERE expr]

Lists non-temporary tables currently open in the table cache.

SHOW PLUGINS

Displays information about server plugins. See also Plugins.

SHOW PRIVILEGES

Lists the supported system privileges.

SHOW PROCESSLIST

Displays which threads are currently running.

SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the relay log of a replica.

SHOW SLAVE HOSTS
SHOW SLAVE STATUS [NONBLOCKING]

Replicas currently registered with the source, and status of the essential replica-thread parameters. See also Replication.

SHOW [GLOBAL|SESSION] STATUS [LIKE 'pattern' | WHERE expr]

Server status information. Many status variables are reset to 0 by FLUSH STATUS.

SHOW TABLE STATUS [{FROM|IN} db] [LIKE 'pattern' | WHERE expr]

Like SHOW TABLES, but with detailed information about each non-TEMPORARY table.

SHOW TRIGGERS [{FROM|IN} db] [LIKE 'pattern' | WHERE expr]

Lists the triggers currently defined on the tables in a database.

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]

Shows the values of system variables.

SHOW GLOBAL VARIABLES LIKE 'max_connections';

SHOW GLOBAL VARIABLES LIKE 'max_connections';

Variable_name	Value
max_connections	151

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS

Errors, warnings, and notes resulting from the last statement in the current session.

-- EXPLAIN / DESCRIBE / DESC are synonymous when listing a table's columns
-- (tbl was created earlier on this page; see the EXPLAIN/DESCRIBE example above)
DESCRIBE tbl;
DESC tbl;

-- EXPLAIN a query in an alternate output format
EXPLAIN FORMAT=JSON SELECT * FROM tbl;

-- EXPLAIN ANALYZE runs the statement and reports actual timing/row counts
EXPLAIN ANALYZE SELECT * FROM Students JOIN Tests;

-- EXPLAIN / DESCRIBE / DESC are synonymous when listing a table's columns
-- (tbl was created earlier on this page; see the EXPLAIN/DESCRIBE example above)
DESCRIBE tbl;
DESC tbl;

-- EXPLAIN a query in an alternate output format
EXPLAIN FORMAT=JSON SELECT * FROM tbl;

-- EXPLAIN ANALYZE runs the statement and reports actual timing/row counts
EXPLAIN ANALYZE SELECT * FROM Students JOIN Tests;

-- A sample of the many SHOW forms
-- (see the SHOW ENGINES and SHOW VARIABLES examples above for their output)
SHOW DATABASES;
SHOW TABLES FROM mydb;
SHOW FULL COLUMNS FROM mydb.tbl;
SHOW CREATE TABLE mydb.tbl;
SHOW INDEXES FROM mydb.tbl;
SHOW TABLE STATUS FROM mydb LIKE 'tbl%';
SHOW ENGINE INNODB STATUS;
SHOW GRANTS FOR 'app_user'@'%';
SHOW PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW WARNINGS;

-- A sample of the many SHOW forms
-- (see the SHOW ENGINES and SHOW VARIABLES examples above for their output)
SHOW DATABASES;
SHOW TABLES FROM mydb;
SHOW FULL COLUMNS FROM mydb.tbl;
SHOW CREATE TABLE mydb.tbl;
SHOW INDEXES FROM mydb.tbl;
SHOW TABLE STATUS FROM mydb LIKE 'tbl%';
SHOW ENGINE INNODB STATUS;
SHOW GRANTS FOR 'app_user'@'%';
SHOW PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW WARNINGS;

MENU

Information Commands

HELP

EXPLAIN, DESCRIBE, DESC

SHOW