Search code examples
mysqldatabaseoracle-databaseexplain

mysql cannot show explain data

I want to see the execute plan of sql : 

explain select name,address from address where id=?

but it said :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?'

  1. does the mysql only support specify value in sql when using explain plan rather than binding variable?
  2. If not, how can I see the execute plan of binding variable? because I think the execute plan of select * from address where id=? is different select * from address where id=2.
  3. Also, when I use explain in mysql, it does not show this sql will be full table access or index access ect like Oracle, so how can I see these information?
Solution

  • You can't see the query plan using ? placeholders, because the query plan is created by the optimizer each time the query is executed based on the specific values provided.

    I think the execute plan of select * from address where id=? is different select * from address where id=2

    If "2" is the bound value, the plan will be the same. There's no such thing as a default plan, without specific values. It is created with each invocation of the prepared statement, because different values may call for a different plan, depending on the results of index dives.

    Also, when I use explain in mysql, it does not show this sql will be full table access or index access ect

    Yes, it does. Not in narrative form, but the information is there for interpretation. See EXPLAIN Output Format in the documentation.

    In brief: your query is doing a full scan if type is either ALL (full table scan) or index (full index scan); on the other hand, if type is ref or eq_ref or range (and some other less common values) then the key column indicates the index being used for equality or range lookups.

    Note that a common novice error is to misinterpret the meaning of Using index in the Extra column. This indicator, when present, means that all the columns needed for the query are present in a single index and that the optimizer will be using that index as a covering index to read data for the query, instead of reading from table data itself -- this flag is not to be confused with index lookups.