Explain in MySql

Explain is the statement used to obtain the query execution plan, or how MySQL will execute our query. You can find information about that plan by adding the EXPLAIN command to the beginning of each query. It works with SELECT, DELETE, INSERT, REPLACE, and UPDATE clauses, and it displays information from the optimizer about the query execution plan.

EXPLAIN is one of the important tools to help understand and optimize MySQL queries. If used properly, it will help us avoid bad queries, just like discovering bottle necks of the system as if they have not pasted the index …

Explain parameters

Let’s find out the result of a simple query through the following example.

EXPLAIN SELECT * FROM Author

Here explain has returned all 10 columns, and we will go into each of them

1. id

The ordinal number for each SELECT statement in your query (in case you use nested subqueries).

2. select_type

Basically, you understand that this is a query type parameter. It includes the following values

• SIMPLE – A single query with no subqueries or unions. The query is a basic SELECT, without any subqueries or union statements (UNION).
• PRIMARY – Query is the outermost SELECT statement of a JOIN operation
• DERIVED – Query is inside a FROM
• SUBQUERY – The first query is in a subquery, regardless of the other query. This query will be executed correctly the first time, after which the results will be cached.
• DEPENDENT SUBQUERY – Query that depends on the query outside it
• UNCACHEABLE SUBQUERY – Query cannot be cached
• UNION – Query is the second SELECT statement of the UNION command
• DEPENDENT UNION – When there is a union in the subquery, and that subquery is of type DEPENDENT SUBQUERY
• UNCACHEABLE UNION – When the uncacheable subquery contains union – UNION RESULT – Query is the result of the UNION command.

3. table

It is just the table name related to the query.

4. type

This field shows how MySQL joins the tables. This is arguably the most important school in the results of explain. It can indicate missing indexes and it may also indicate that your query needs reconsideration. The values ​​of this field are:

• system – The table has 0 or 1 row
• const – The table has only 1 indexed row that matches the search criteria. This is the fastest type of join, because the table needs to be read only once and the value of the column is considered as a constant when joining with other tables.
• eq_ref – Like const, the field used does not stand alone but in the JOIN statement. This is the second best type of join after const.
• ref – When the field being searched is indexed, however it is not UNIQUE. This type of join usually happens with columns that are compared with the = or <=> operators
• fulltext – Join tables using the FULLTEXT index
• ref_or_null – Almost like ref but contains both rows with columns with null values
• index_merge – The join operation uses a list of indexes to produce the result set. The KEY column will list the keys used
• unique_subquery – Subqueries with the IN command will return only one result and use the primary key
• index_subquery – Almost like unique_subquery but returns more than one line
• range – An index is used to find matching rows within a specified range when the key is compared to a constant through operators BETWEEN, IN,>,> =, …
• index – The entire index tree is approved to find a row that meets the condition, so it will be very slow
• all – The entire table is scanned to find matching rows for the join. This type of join is considered the worst and often shows the lack of indexes on tables

5. possible_keys

List all the keys by MySql to find the rows in the table. These keys may or may not be used in practice

6. key

The key is the official MySql used to make the index to search. This column may contain keys not listed in the possible_keys column

7. key_len

Displays the length of the index optimizer to select to use. For example, key_len = 2 means you need memory to store 2 characters

8. ref

Displays columns or constants that are compared to the index listed in the key column. In case the query is JOIN, this is the key value of the corresponding table that is joined with the main table

9. rows

It shows the number of lines mysql “intends” to fetch from the table in that query. This is a very important indicator, especially when you use JOINs or subqueries

One thing to note is that when the query is of type “DERIVED”, it will be a subquery in the FROM statement, then without my query, mysql will not be able to “guess” the number of rows to retrieve. Therefore EXPLAIN will be quite time consuming if the subquery is heavy

10. extra

This is also a very important parameter. Values ​​such as Using Temporary, Using filesort, … of this column may indicate that a query is not very good. Just by looking at the extra, you can guess what is going to happen behind a certain query

Conclude

Through the value of id and select_type, we can know the order of access to the tables to get something, how to combine with other tables.

Through the value of type, key, ref and rows, we can know for each table what information will be fetched, access to which table will be heavy, so we can index for that table.

Through the value of the extra, we will have a quick overview of the query

Refer

https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/