MySQL source code learning: Rows_examined = 0 in slow query logs

Before explaining this problem, we should first point out two related backgrounds: 1. MySQL temporary tables are all MyISAM. 2. The total number of records in the MyISAM table is stored additionally. count (*)

Before explaining this problem, we should first point out two related backgrounds: 1. MySQL temporary tables are all MyISAM. 2. The total number of records in the MyISAM table is stored additionally. count (*)

Recently, a DBA asked a question: why is Rows_examined: 0 displayed in many slow query logs?

It should be noted that such slow query statements are similar to select count (*) from (...) T;

Before explaining this problem, we should first point out two background:

1. MySQL temporary tables are all MyISAM tables.

2. The total number of records in the MyISAM table is stored Additionally, and data does not need to be traversed during count.

3. Converting count (*) to a const value is done in the optimize stage.

Problem Analysis:

This value corresponds to examined_row_count in the code, which is used to count the number of records actually scanned during each execution.

Normal process:

During the query execution, the information of each subquery is in curr_join, where curr_join-> examined_rows ++ each time a row is scanned. after the subquery is complete, curr_join-> examined_rows accumulates into examined_row_count.

Where can I clear 0?

In the preceding statement, for subqueries in from, curr_join-> examined_rows is normal, but when count is calculated externally, the optimization results mentioned above do not need to scan the table at this stage, and set thd-> examined_row_count to 0. The signature code is in JOIN: exec.

From the comments in the code, it seems that it is a place that is not carefully considered and to be verified.

Improvement Analysis:

Even if there are many reasons, the 0 displayed in the slow query log is unfriendly and can be understood as a bug.

In fact, from the above analysis, if it is a link in a composite query, especially not the first link, clearing 0 here will cause an error in the display result. From the current thd information, you can determine whether a subquery is used. Make a simple modification and check whether the subquery is cleared Based on thd. derived_tables.

In fact, this value is reset before each execution starts, and there is reason to suspect that this field can actually be deleted directly. This is radical and requires evidence.

Simple Verification:

After thd. derived_tables is added,

To facilitate debugging, all the queries are routed to slow_log.