Understand EXPLAIN in a mysql query -

i trying interpret explain of mysql in query(written in 2 different ways),this table:

    create table text_mess(     datamess timestamp(3) default 0,     sender bigint ,     recipient bigint ,     roger boolean,     msg char(255),     foreign key(recipient)              references users (tel)                          on delete cascade                         on update cascade, primary key(datamess,sender) ) engine = innodb 

this first type of query :

    explain     select /*!straight_join*/datamess, sender,recipient,roger,msg     text_mess join (select max(datamess)as dmess                     text_mess                      roger = true                     group sender,recipient) max                     on text_mess.datamess=max.dmess ;  

and second:

    explain     select /*!straight_join*/datamess, sender,recipient,roger,msg      (select max(datamess)as dmess                     text_mess                      roger = true                     group sender,recipient) max       join     text_mess     on max.dmess = text_mess.datamess ; 

the 2 queries asking same thing, difference order of ref_table (driving_table), in first case text_mess, in second case sub query. first result:

    id  select_type     table       type    possible_keys   key     key_len      ref                            rows            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------     1   primary     text_mess           primary         null        null    null                            294225      null     1   primary     derived2        ref     auto_key0       auto_key0   7       inextremis.text_mess.datamess   10          using index     2   derived     text_mess           recipient       null        null    null                            294225      using where; using temporary; using filesort 

second result:

    id  select_type     table       type    possible_keys   key          key_len    ref     rows        ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------     1   primary      <derived2>         null            null         null      null     294225  using     1   primary      text_mess      ref     primary         primary         6      max.dmess    1   null     2   derived      text_mess          recipient       null         null      null     294225  using where; using temporary; using filesort 

as can see difference in order of first 2 lines, question in particular on second (the faster query) second line should inner-table, if so, why column ref tells me: max.dmess, should column of ref-table (sub-query).

is last row referred on how first built? in end, think there more efficient query?

i think answer table scan vs primary key. if see, first query, mysql not use keys ready every row table "text_mess":

    id  select_type     table       type    possible_keys   key     key_len      ref                            rows            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------       1   primary     text_mess           primary         null        null    null                            294225      null 

but use "on" statement on "derived2" table , mysql create auto key:

    id  select_type     table       type    possible_keys   key     key_len      ref                            rows            ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------     1   primary     derived2        ref     auto_key0       auto_key0   7       inextremis.text_mess.datamess   10          using index 

this key not exists, mysql should create it.

if take second example, full table scan happens on "derived2" table, , primary key "text_mess" in use:

    id  select_type     table       type    possible_keys   key          key_len    ref     rows        ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------     1   primary      <derived2>         null            null         null      null     294225  using     1   primary      text_mess      ref     primary         primary         6      max.dmess    1   null 

the answer is, on such situation mysql decided create , use index, makes full table scan, , faster.

imagine auto_key0 key includes 1 column subquery again. process not necessary. why second query faster.