Interpreting the MySQL Explain command

Question

Interpreting the MySQL Explain command

Navigation

#1 by (2 votes)

5

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'vealwaysmadeoptimizationsusingonlytables,buttheabovequeryusesaview(get_category).

Icannotunderstandthefirstline,whereitsays"Table: derived2 / Rows: 23248".

How would you interpret the above result taking into account that I'm using a View.

mysql

asked by anonymous 15.10.2014 / 13:21

1 answer

Error in rbind: "Error in match.names (clabs, names (xi))" Isolated Storage or SQL Server Compact

score 2 · Answer 1

In the MySQL online documentation you can find the explanation for all the aspects in the explanation EXPLAIN (English) .

derivedN

Regarding your particular question, we can read here < sup> (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 for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.

What 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 joining 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. The derived table can result, for example, from a subquery in the FROM clause.

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

rows

The second part of your question, we can read here < sup> (English) :

The rows column indicates the number of rows MySQL believes it should examine to execute the query.

What translated:

The rows column indicates the number of rows that MySQL thinks it should examine to execute the query.

In your case, MySQL thinks you should look at 23248 lines to get the result.