gistfile1.txt

explain select * from range_test where '2000-01-01 00:00:00' <= datetime_col and datetime_col <= '2070-01-01 00:00:00' | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `range_test`.`id` AS `id`,`range_test`.`int_col` AS `int_col`,`range_test`.`bigint_col` AS `bigint_col`,`range_test`.`datetime_col` AS `datetime_col` from `range_test` where (('2000-01-01 00:00:00' <= `range_test`.`datetime_col`) and (`range_test`.`datetime_col` <= '2070-01-01 00:00:00'))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(('2000-01-01 00:00:00' <= `range_test`.`datetime_col`) and (`range_test`.`datetime_col` <= '2070-01-01 00:00:00'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(('2000-01-01 00:00:00' <= `range_test`.`datetime_col`) and (`range_test`.`datetime_col` <= '2070-01-01 00:00:00'))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(('2000-01-01 00:00:00' <= `range_test`.`datetime_col`) and (`range_test`.`datetime_col` <= '2070-01-01 00:00:00'))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(('2000-01-01 00:00:00' <= `range_test`.`datetime_col`) and (`range_test`.`datetime_col` <= '2070-01-01 00:00:00'))"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`range_test`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`range_test`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 499060,
                    "cost": 101258
                  },
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_int",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_bigint",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_datetime",
                      "usable": true,
                      "key_parts": [
                        "datetime_col",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_datetime",
                        "ranges": [
                          "2000-01-01 00:00:00 <= datetime_col <= 2070-01-01 00:00:00"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 83892,
                        "cost": 100671,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_datetime",
                      "rows": 83892,
                      "ranges": [
                        "2000-01-01 00:00:00 <= datetime_col <= 2070-01-01 00:00:00"
                      ]
                    },
                    "rows_for_plan": 83892,
                    "cost_for_plan": 100671,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`range_test`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "rows": 83892,
                      "cost": 117450,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 117450,
                "rows_for_plan": 83892,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(('2000-01-01 00:00:00' <= `range_test`.`datetime_col`) and (`range_test`.`datetime_col` <= '2070-01-01 00:00:00'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`range_test`",
                  "attached": "(('2000-01-01 00:00:00' <= `range_test`.`datetime_col`) and (`range_test`.`datetime_col` <= '2070-01-01 00:00:00'))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`range_test`",
                "pushed_index_condition": "(('2000-01-01 00:00:00' <= `range_test`.`datetime_col`) and (`range_test`.`datetime_col` <= '2070-01-01 00:00:00'))",
                "table_condition_attached": null,
                "access_type": "range"
              }
            ]
          }
        ]
      }
    },
    {
      "join_explain": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}