Database Internals

CREATE TABLE `t` (
  `id` varchar(10) NOT NULL,
  `remark` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_remark` (`remark`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO t
VALUES ('AAAA', 'ZZ'), 
('BBBB', 'XX'),
('CCCC', 'YY'),
('DDD', 'VVVVV');

void Optimize_table_order::best_access_path(JOIN_TAB *tab,
                                            const table_map remaining_tables,
                                            const uint idx, bool disable_jbuf,
                                            const double prefix_rowcount,
                                            POSITION *pos)
...
if (!best_ref && idx == join->const_tables && table == join->sort_by_table && join->query_expression()->select_limit_cnt >= rows_fetched) {
	trace_access_scan.add("use_tmp_table", true);
  join->sort_by_table = (TABLE *)1;  // Must use temporary table
}

1  Process 58726 stopped
2  * thread #40, name = 'connection', stop reason = step over
3      frame #0: 0x0000000100a84208 mysqld`Optimize_table_order::best_access_path(this=0x0000000171754e08, tab=0x0000000134812c00, remaining_tables=1, idx=0, disable_jbuf=true, prefix_rowcount=1, pos=0x0000000134812db8) at sql_planner.cc:1227:8
4     1224	  pos->loosescan_key = MAX_KEY;
5     1225	  pos->use_join_buffer = best_uses_jbuf;
6     1226
7  -> 1227	  if (!best_ref && idx == join->const_tables && table == join->sort_by_table &&
8     1228	      join->query_expression()->select_limit_cnt >= rows_fetched) {
9     1229	    trace_access_scan.add("use_tmp_table", true);
10    1230	    join->sort_by_table = (TABLE *)1;  // Must use temporary table
11 Target 0: (mysqld) stopped.
12 (lldb) p rows_fetched
13 (double) 4
14 (lldb) p join->query_expression()->select_limit_cnt
15 (ha_rows) 18446744073709551615
...

mysql> SET OPTIMIZER_TRACE='enabled=on',END_MARKERS_IN_JSON=on;

mysql> SELECT * FROM t ORDER BY remark LIMIT 3;
+------+--------+
| id   | remark |
+------+--------+
| DDD  | VVVVV  |
| BBBB | XX     |
| CCCC | YY     |
+------+--------+

mysql> SELECT * FROM information_schema.optimizer_trace;

{
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 4,
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 0.65,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 4,
                "cost_for_plan": 0.65,
                "chosen": true
              }
            ] /* considered_execution_plans */
          }

bool JOIN::optimize(bool finalize_access_paths)
...
/*
  If we decided to not sort after all, update the cost of the JOIN.
  Windowing sorts are handled elsewhere
*/
if (sort_cost > 0.0 &&
    !explain_flags.any(ESP_USING_FILESORT, ESC_WINDOWING)) {
  best_read -= sort_cost;
  sort_cost = 0.0;
}

1  Process 58726 stopped
2  * thread #40, name = 'connection', stop reason = step over
3      frame #0: 0x0000000100a1fcd0 mysqld`JOIN::optimize(this=0x000000010ee26710, finalize_access_paths=true) at sql_optimizer.cc:1035:8
4     1032	    Windowing sorts are handled elsewhere
5     1033	  */
6     1034	  if (sort_cost > 0.0 &&
7  -> 1035	      !explain_flags.any(ESP_USING_FILESORT, ESC_WINDOWING)) {
8     1036	    best_read -= sort_cost;
9     1037	    sort_cost = 0.0;
10    1038	  }
11 Target 0: (mysqld) stopped.
12 (lldb) p sort_cost
13 (double) 4
14 (lldb) p best_read
15 (double) 4.649

1  Process 58726 stopped
2  * thread #40, name = 'connection', stop reason = step over
3      frame #0: 0x0000000100a1fcec mysqld`JOIN::optimize(this=0x000000010ee26710, finalize_access_paths=true) at sql_optimizer.cc:1034:7
4     1031	    If we decided to not sort after all, update the cost of the JOIN.
5     1032	    Windowing sorts are handled elsewhere
6     1033	  */
7  -> 1034	  if (sort_cost > 0.0 &&
8     1035	      !explain_flags.any(ESP_USING_FILESORT, ESC_WINDOWING)) {
9     1036	    best_read -= sort_cost;
10    1037	    sort_cost = 0.0;
11 (lldb) p explain_flags
12 (Explain_format_flags)  (sorts = "\0\U00000001")

/*
enum Explain_sort_clause {
  ESC_none = 0,
  ESC_ORDER_BY = 1,
  ESC_GROUP_BY = 2,
  ESC_DISTINCT = 3,
  ESC_BUFFER_RESULT = 4,
  ESC_WINDOWING = 5,
  //-----------------
  ESC_MAX
};

enum Explain_sort_property {
  ESP_none = 0,
  ESP_EXISTS = 1 << 0,     ///< Original query has this clause
  ESP_IS_SIMPLE = 1 << 1,  ///< Clause is effective for single JOIN_TAB only
  ESP_USING_FILESORT = 1 << 2,  ///< Clause causes a filesort
  ESP_USING_TMPTABLE = 1 << 3,  ///< Clause creates an intermediate table
  ESP_DUPS_REMOVAL = 1 << 4     ///< Duplicate removal for DISTINCT
};

## opt_explain_format.h
...
  bool any(Explain_sort_property property,
           Explain_sort_clause clause = ESC_none) const {
    for (size_t i = ESC_none + 1; i <= ESC_MAX - 1; i++) {
      if (i != clause && (sorts[i] & property)) return true;
    }
    return false;
  }
...
*/

1  Process 27633 stopped
2  * thread #40, name = 'connection', stop reason = step over
3      frame #0: 0x0000000100a1fd10 mysqld`JOIN::optimize(this=0x000000010f8b70d8, finalize_access_paths=true) at sql_optimizer.cc:1038:3
4     1035	      !explain_flags.any(ESP_USING_FILESORT, ESC_WINDOWING)) {
5     1036	    best_read -= sort_cost;
6     1037	    sort_cost = 0.0;
7  -> 1038	  }
8     1039
9     1040	  count_field_types(query_block, &tmp_table_param, *fields, false, false);
10    1041
11 Target 0: (mysqld) stopped.
12 (lldb) p best_read
13 (double) 1.399
14 (lldb) p sort_cost
15 (double) 0

# sql_select.cc
1 (lldb) p join->sort_cost
2 (double) 0
3 (lldb) p join->best_read
4 (double) 0.64900000000000002