How MySQL Calculates Full Table Scan Cost: Formulas, Stats, and Constants Explained

1. Overview

MySQL's query optimizer relies on a cost model, and the cost of a full‑table scan serves as the baseline for comparing other access methods.

2. Calculation Formula

The full‑table‑scan cost is calculated as:

full_table_scan_cost = io_cost + 1.1 + cpu_cost + 1

Both 1.1 and 1 are hard‑coded constants.

class Cost_estimate {
private:
  // cost of I/O operations
  double io_cost;
  // cost of CPU operations
  double cpu_cost;
  // cost of remote operations
  double import_cost;
  // memory used (bytes)
  double mem_cost;
  ...
};

The io_cost and cpu_cost are defined as:

io_cost = cluster_page_count * avg_single_page_cost

cpu_cost = n_rows * 0.1

where cluster_page_count is the number of primary‑key index pages, and n_rows is the table row count, both obtained from InnoDB statistics.

avg_single_page_cost = pages_in_memory_percent * 0.25 + pages_on_disk_percent * 1.0 pages_in_memory_percent is the proportion of primary‑key leaf pages cached in the Buffer Pool, while pages_on_disk_percent = 1 - pages_in_memory_percent.

The constants 0.25 (memory_block_read_cost) and 1.0 (io_block_read_cost) are default values from mysql.engine_cost.

3. Statistics

MySQL obtains cluster_page_count and n_rows from the InnoDB table statistics tables:

SELECT table_name, n_rows, clustered_index_size
FROM mysql.innodb_table_stats
WHERE database_name = 'sakila' AND table_name = 'city';

SELECT NAME, NUM_ROWS, CLUST_INDEX_SIZE
FROM information_schema.INNODB_TABLESTATS
WHERE NAME = 'sakila/city';

4. Data Pages in Memory Ratio

The ratio of cached leaf pages is calculated by:

inline double index_pct_cached(const dict_index_t *index) {
  const ulint n_leaf = index->stat_n_leaf_pages;
  const uint64_t n_in_mem = buf_stat_per_index->get(index_id_t(index->space, index->id));
  const double ratio = static_cast<double>(n_in_mem) / n_leaf;
  return std::max(std::min(ratio, 1.0), 0.0);
}

InnoDB maintains a hash table buf_stat_per_index->m_store that tracks how many leaf pages of each primary‑key index are currently cached in the Buffer Pool.

5. Cost Constants

The constants memory_block_read_cost and io_block_read_cost are read from mysql.engine_cost and can be overridden by updating cost_value:

SELECT cost_name, cost_value, default_value
FROM mysql.engine_cost;

The row_evaluate_cost constant (default 0.1) is read from mysql.server_cost and can also be modified.

6. Summary

The essential formula for full‑table‑scan cost is:

full_table_scan_cost = io_cost + 1.1 + cpu_cost + 1 io_cost is derived from the average page read cost multiplied by the number of primary‑key pages, while cpu_cost depends on the row count. Adjusting the underlying cost constants or the cached‑page ratio directly influences the optimizer's cost estimates.