13.8 Utility Statements

 File: manual.info.tmp, Node: describe, Next: explain, Prev: sql-utility-statements, Up: sql-utility-statements

13.8.1 DESCRIBE Statement

The note 'DESCRIBE': describe. and note 'EXPLAIN': explain. statements are synonyms, used either to obtain information about table structure or query execution plans. For more information, see note show-columns::, and note explain::.

 File: manual.info.tmp, Node: explain, Next: help, Prev: describe, Up: sql-utility-statements

13.8.2 EXPLAIN Statement

 {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 note 'DESCRIBE': describe. and note 'EXPLAIN': explain. statements are synonyms. In practice, the note 'DESCRIBE': describe. keyword is more often used to obtain information about table structure, whereas note 'EXPLAIN': explain. is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).

The following discussion uses the note 'DESCRIBE': describe. and note 'EXPLAIN': explain. keywords in accordance with those uses, but the MySQL parser treats them as completely synonymous.

Obtaining Table Structure Information

*note 'DESCRIBE': describe. provides information about the columns in a table:

 mysql> DESCRIBE City;
 +------------+----------+------+-----+---------+----------------+
 | Field      | Type     | Null | Key | Default | Extra          |
 +------------+----------+------+-----+---------+----------------+
 | Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
 | Name       | char(35) | NO   |     |         |                |
 | Country    | char(3)  | NO   | UNI |         |                |
 | District   | char(20) | YES  | MUL |         |                |
 | Population | int(11)  | NO   |     | 0       |                |
 +------------+----------+------+-----+---------+----------------+

note 'DESCRIBE': describe. is a shortcut for note 'SHOW COLUMNS': show-columns. These statements also display information for views. The description for note 'SHOW COLUMNS': show-columns. provides more information about the output columns. See note show-columns::.

By default, *note 'DESCRIBE': describe. displays information about all columns in the table. COL_NAME, if given, is the name of a column in the table. In this case, the statement displays information only for the named column. WILD, if given, is a pattern string. It can contain the SQL '%' and '_' wildcard characters. In this case, the statement displays output only for the columns with names matching the string. There is no need to enclose the string within quotation marks unless it contains spaces or other special characters.

The *note 'DESCRIBE': describe. statement is provided for compatibility with Oracle.

The note 'SHOW CREATE TABLE': show-create-table, note 'SHOW TABLE STATUS': show-table-status, and note 'SHOW INDEX': show-index. statements also provide information about tables. See note show::.

Obtaining Execution Plan Information

The *note 'EXPLAIN': explain. statement provides information about how MySQL executes statements:

note 'EXPLAIN': explain. requires the same privileges required to execute the explained statement. Additionally, note 'EXPLAIN': explain. also requires the 'SHOW VIEW' privilege for any explained view.

With the help of note 'EXPLAIN': explain, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use note 'EXPLAIN': explain. to check whether the optimizer joins the tables in an optimal order. To give a hint to the optimizer to use a join order corresponding to the order in which the tables are named in a note 'SELECT': select. statement, begin the statement with 'SELECT STRAIGHT_JOIN' rather than just note 'SELECT': select. (See *note select::.)

The optimizer trace may sometimes provide information complementary to that of note 'EXPLAIN': explain. However, the optimizer trace format and content are subject to change between versions. For details, see note optimizer-tracing::.

If you have a problem with indexes not being used when you believe that they should be, run note 'ANALYZE TABLE': analyze-table. to update table statistics, such as cardinality of keys, that can affect the choices the optimizer makes. See note analyze-table::.

Note:

MySQL Workbench has a Visual Explain capability that provides a visual representation of *note 'EXPLAIN': explain. output. See Tutorial: Using Explain to Improve Query Performance (https://dev.mysql.com/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html).

 File: manual.info.tmp, Node: help, Next: use, Prev: explain, Up: sql-utility-statements

13.8.3 HELP Statement

 HELP 'SEARCH_STRING'

The note 'HELP': help. statement returns online information from the MySQL Reference Manual. Its proper operation requires that the help tables in the 'mysql' database be initialized with help topic information (see note server-side-help-support::).

The *note 'HELP': help. statement searches the help tables for the given search string and displays the result of the search. The search string is not case-sensitive.

The search string can contain the wildcard characters '%' and '_'. These have the same meaning as for pattern-matching operations performed with the 'LIKE' operator. For example, 'HELP 'rep%'' returns a list of topics that begin with 'rep'.

The 'HELP' statement does not require a terminator such as ';' or ''.

The HELP statement understands several types of search strings:

In other words, the search string matches a category, many topics, or a single topic. The following descriptions indicate the forms that the result set can take.

 File: manual.info.tmp, Node: use, Prev: help, Up: sql-utility-statements

13.8.4 USE Statement

 USE DB_NAME

The *note 'USE': use. statement tells MySQL to use the named database as the default (current) database for subsequent statements. This statement requires some privilege for the database or some object within it.

The named database remains the default until the end of the session or another *note 'USE': use. statement is issued:

 USE db1;
 SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
 USE db2;
 SELECT COUNT(*) FROM mytable;   # selects from db2.mytable

The database name must be specified on a single line. Newlines in database names are not supported.

Making a particular database the default by means of the *note 'USE': use. statement does not preclude accessing tables in other databases. The following example accesses the 'author' table from the 'db1' database and the 'editor' table from the 'db2' database:

 USE db1;
 SELECT author_name,editor_name FROM author,db2.editor
   WHERE author.editor_id = db2.editor.editor_id;

 File: manual.info.tmp, Node: innodb-storage-engine, Next: storage-engines, Prev: sql-statements, Up: Top

14 The InnoDB Storage Engine ****************************

 File: manual.info.tmp, Node: innodb-introduction, Next: mysql-acid, Prev: innodb-storage-engine, Up: innodb-storage-engine