Tutorial :What is the significance of the order of statements in mysql explain output?



Question:

This is mysql explain plan for one of the query I am looking into.

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+  | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |  +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+  |  1 | SIMPLE      | table2 | index | NULL          | PRIMARY | 4       | NULL |    6 |       |   |  1 | SIMPLE      | table3 | ALL   | NULL          | NULL    | NULL    | NULL |   23 |       |   |  1 | SIMPLE      | table1 | ALL   | NULL          | NULL    | NULL    | NULL |    8 |       |   |  1 | SIMPLE      | table5 | index | NULL          | PRIMARY | 4       | NULL |    1 |       |   +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+  

4 rows in set (0 sec)

What is the significance of the order of statements in this output ? Does it mean that table5 is read before all others ?


Solution:1

The tables are listed in the output in the order that MySQL would read them while processing the query. You can read more about the Explain plan output here.

Additionally, the output tells me:

  • The optimizer saw the query as having four (4) SELECT statements within it. Being a "simple" select type, those queries are not using UNION or subqueries.
  • Two of those statements could use indexes (based on the type column), which were primary keys (based on the key column). The other two could not use any indexes.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »