Interpret the Mysql Explain command

Question

I have a query that is taking 6/8 seconds to execute.

The database I’m using is Mysql.

In Phpmyadmin I did the following:

EXPLAIN SELECT id_categoria, sc.categoria, categoria_principal, associada FROM 
get_produto_categoria gpc INNER JOIN shop_categoria sc USING(id_categoria) WHERE
gpc.id_produto = 2254 ORDER BY categoria_principal DESC, associada DESC, sc.ord ASC,
sc.id_categoria ASC

See below the query result:

I have always done optimizations using only tables, but the above query uses a view (get_product_category).

1. I can’t understand the first line, where it says "Table: derived2 / Rows:23248".

2. How would the interpretation of the above result take into account that I am using a View.

Answer 1

In the online documentation of Mysql you can find the explanation for all the aspects present in the explanation EXPLAIN ^(English).

derivedN

Regarding your particular doubt, we can read here ^(English):

The name of the table to which the Row of output refers. This can also be one of the following values:

<unionM,N>: The Row refers to the Union of the Rows with id values of M and N.

<derivedN>: The Row refers to the derived table result for the Row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.

That translated:

The name of the table to which the output line refers. This can also be one of the following values:

<unionM,N>: The line refers to the union of lines with ID values of M and N.

<derivedN>: The row refers to the derived table result for the row with an identification value of N. The derived table may result, for example, from a sub-allowance in the FROM clause.

Your current result derived2 indicates that the row results from your derived table shop_categoria referring to ID #2.

Rows

The second part of your doubt, we can read here ^(English):

The Rows column indicates the number of Rows Mysql believes it must examine to execute the query.

That translated:

The column Rows indicates the number of rows Mysql thinks you should examine to run the query.

In your case, Mysql thinks you should examine 23248 lines to get the result.