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?

  • 1
    In practice, the DESCRIBE keyword is more often used to obtain information about table structure This is true of course, but it is not useful in this particular case. SHOW CREATE TABLE is more useful. – Akina Nov 17 at 5:23

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.