Understanding MySQL Full‑Table‑Scan Data Access and Field Filtering Process

The author, Gao Peng, shares a detailed analysis of MySQL's internal workflow when executing a full‑table‑scan query, focusing on how the number of selected fields influences performance.

Problem Origin – Although the execution plan is identical (full table scan) for the test cases, the number of fields retrieved differs, leading to varying execution times. The article provides function interfaces and stack‑frame traces for reference.

Simple Process Overview

The core interface is row_search_mvcc , which performs the following steps:

Build read_set (MySQL layer) – For each field to be accessed, TABLE::mark_column_used sets a bit in the read_set bitmap. Example code: case MARK_COLUMNS_READ: bitmap_set_bit(read_set, field->field_index);

Construct template (Innodb layer) – A mysql_row_templ_t template is built based on the read_set. The key check is performed with: bitmap_is_set(table->read_set, static_cast<uint>(i))

Initial cursor positioning (Innodb layer) – For a full scan, the cursor is opened at the first leaf page using btr_cur_open_at_index_side_func instead of the usual btr_pcur_open_with_no_init_func .

Fetch the first row (Innodb layer) – The row is retrieved via: rec = btr_pcur_get_rec(pcur); // fetch full row from persistent cursor

Convert the row to MySQL format (Innodb layer) – The conversion loops over the template fields: for (i = 0; i < prebuilt->ntemplate; i++) and handles each data type, e.g., integer conversion: case DATA_INT: ptr = dest + len; for (;;) { ptr--; *ptr = *data; // copy value if (ptr == dest) { break; } data++; }

Apply WHERE filtering (MySQL layer) – The condition is evaluated with: found = MY_TEST(condition->val_int());

Iterate to next rows – The cursor moves to the next record, the row is converted again using the same template, and WHERE filtering is applied until all rows are processed.

Key Differences When Field Count Changes

Read_set bitmap contains more set bits for more fields.

More template entries are created, increasing conversion loops.

Each row conversion to MySQL format takes longer as more fields are processed.

Commonalities

The number of rows accessed remains the same.

The overall access flow is identical.

WHERE filtering is performed in the same way.

The most time‑consuming part is the per‑row conversion to MySQL format, especially when many fields are selected. Therefore, selecting only the needed columns (instead of * ) can significantly improve performance.

Finally, the author recommends reading his book “Deep Understanding of MySQL Master‑Slave Principles – 32 Lectures” for a thorough grasp of MySQL replication and internals.