Why is there a separate describe and explain statement in MySQL

Posted on

Question :

I usually use DESCRIBE to get a description of a table, or a query. But, I was recently looking up some information on how to interpret the output of a DESCRIBE SELECT ... statement, and found that all the tutorials used EXPLAIN rather than DESCRIBE.

I found in the MySQL docs, this explanation:

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas 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 DESCRIBE and EXPLAIN keywords in accordance with those uses, but the MySQL parser treats them as completely synonymous.

So, I’m curious why there are two separate commands. Is this needed to conform to the ANSI SQL spec, or is there a historic reason for the two separate commands?

Answer :

This is another example of the liberties the MySQL developers have been taking when interpreting the SQL standard (if they ever read it, that is).

DESCRIBE [OUTPUT], with slightly different semantics, is a statement prescribed by the SQL standard, and its purpose is to provide information about the result set (column names and data types) of an executable SQL statement that returns a cursor (result set). This information is inserted to the SQL Descriptor Area (SQLDA) that an application can then access in order to properly process the result set. The standard does not allow for the form DESCRIBE <table name>. This means that the DESCRIBE implementation by MySQL is non-standard.

EXPLAIN is not a part of the SQL standard, but most major DBMSes use it, with slightly different syntax, to generate a statement execution plan in some form. It applies to all executable statements, not just those that return cursors. In that sense the MySQL implementation of EXPLAIN <statement> follows this convention.

Leave a Reply

Your email address will not be published. Required fields are marked *