Understand EXPLAIN in a mysql query

Question

I am trying to interpret the explain of mysql in a query(written in two different ways),this is the 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 is the first type of query :

    EXPLAIN
    select /*!STRAIGHT_JOIN*/datamess, sender,recipient,roger,msg
    from text_mess join (select max(datamess)as dmess
                    from text_mess 
                    where roger = true
                    group by sender,recipient) as max
                    on text_mess.datamess=max.dmess ; 

and this is the second:

    EXPLAIN
    select /*!STRAIGHT_JOIN*/datamess, sender,recipient,roger,msg
    from  (select max(datamess)as dmess
                    from text_mess 
                    where roger = true
                    group by sender,recipient) as max
      join
    text_mess
    on max.dmess = text_mess.datamess ;

the two queries are asking the same thing, the only difference is the order of ref_table (driving_table), in the first case is text_mess, in the second case is a sub query. first result:

    id  select_type     table       type    possible_keys   key     key_len      ref                            rows        Extra
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   PRIMARY     text_mess       ALL     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       ALL     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    Extra
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   PRIMARY      <derived2>     ALL     null            null         null      null     294225  Using where
    1   PRIMARY      text_mess      ref     PRIMARY         PRIMARY         6      max.dmess    1   null
    2   DERIVED      text_mess      ALL     recipient       null         null      null     294225  Using where; Using temporary; Using filesort

As you can see the difference is in the order of the first two lines, my question in particular is on the second (the faster query) the second line should be the inner-table, but if so, why the column ref tells me: max.dmess, that should be the column of the ref-table (sub-query).

Is the last row referred on how the first is built? In the end, you think there is a more efficient query?

Oat

On INNER JOINs it's recommended to put the table with less rows on the left (i.e. the first table). The MySQL query planner is able to swap the tables for you if you put the biggest table first (it produces the same result on EXPLAIN, no matter what table you put first). In this case it cannot perform this magic because on your queries one of the JOINed table is not a table but a subquery and this prevents it to know which table is smaller. – Flub

My question was the result of explain: in particular the rows with id 1, and is as follows: it is right to interpret the first row of the result of explain as the "processing" of the table to the left of the join and the second row as the "processing" of the table that is to the right of the join, finally the last line is "processing" of the table <derived2>. and rightly interpret (in the second query explain) the max.dmess of ref column on the second row, as the column from which each line is compared to the rows of the table text_mess (found via key: PRIMARY) – Oat

For better performance, create index roger asc, sender asc, recipient asc, datamess desc (or all desc). – Streptokinase

Can you run an analyze on the tables and re-run the queries. Do they still have same explain ? – Shun

Answer 1

I think the answer is the table scan vs primary key. If you see, by the first query, MySQL does not use any keys but ready every row from the table "text_mess":

    id  select_type     table       type    possible_keys   key     key_len      ref                            rows        Extra
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

     1   PRIMARY     text_mess       ALL     PRIMARY         null        null    null                            294225      null

But you use an "ON" statement on the "derived2" table and MySQL will create an auto key:

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

This key is not exists, so MySQL should create it.

If you take your second example, the full table scan happens on the "derived2" table, and your PRIMARY KEY from the "text_mess" is in use:

    id  select_type     table       type    possible_keys   key          key_len    ref     rows    Extra
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   PRIMARY      <derived2>     ALL     null            null         null      null     294225  Using where
    1   PRIMARY      text_mess      ref     PRIMARY         PRIMARY         6      max.dmess    1   null

The answer is, that on such a situation MySQL decided to create and use an index, but generally it makes a full table scan, and it is simply faster.

Imagine that the auto_key0 key includes the only one column from this subquery again. This extra process is not necessary. That is why your second query is faster.

Unwashed