CODEWITHSUNDEEP
mysqlsql
王奕然
王奕然

Reputation: 4049

explain plan meanings in mysql

i use explain plan,but i am confused what is its real meaning.

explain extended 
select * 
from (select type_id from con_consult_type cct  
      where cct.consult_id = (select id 
                              from con_consult 
                              where id = 1)) 
      cctt left join con_type ct on cctt.type_id = ct.id;

the results is enter image description here

i google the derived is temporary table,but what is its sql of the temporary table?is ctt table? and the step 2,is result of cctt left join con_type ct on cctt.type_id = ct.id? the FK_CONSULT_TO_CONSULT_TYPE is consult_id refer con_consult id column, how to use the index in the sql? get all results of ctt,and then use the index filter?

please help me explain what the explain meanings.

Upvotes: 1

Views: 56

Answers (1)

harvey
harvey

Reputation: 2953

This is a bad query to learn the basics of the explain output, there is simply too much happening with all the sub queries, and joins.

I can give a run down of some of the essentials;

  • 'rows' column: Less is better, it shows how many rows had to be scanned by the database, anything less than a couple of hundred is good, generally indicates how well it is able to find your data from the indexes;
  • 'possible_keys': and 'keys': If 'rows' is big, you may have to tweek your keys to provide the engine with some help finding your data
  • 'type': Type of join

To answer some of your questions;

  • 'sql of the temporary table' - it's the first subquery in your sql
  • With FK_CONSULT_TO_CONSULT_TYPE you dont have to do anything, the engine has allready picked this up as an index which is what the explain is saying.
  • Queries are broken into 3 essentials steps; select data, filter, and join. Each row in the explain is a detail into one or more of these operations, it may not necessarily relate to a specific section of your SQL as the engine may have combined various parts into one.

Upvotes: 1

Related Questions