MySQL Query Tracer Optimiser Steps (Redacted response)

mysql_query_optimizer_steps.json

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "join_preparation": {
              "select#": 2,
              "steps": ["
                {
                  "IN_uses_bisection": true
                },
                {
                  "join_preparation": {
                    "select#": 3,
                    "steps": [
                      {
                        "expanded_query": "
                      },
                      {
                        "transformation": {
                          "select#": 3,
                          "from": "IN (SELECT)",
                          "to": "semijoin",
                          "chosen": true
                        }
                      }
                    ]
                  }
                }
              ]
            }
          },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": ",
              "steps": [
              ...
              ]
            }
          },
            "rows_estimation": [
              {
                "table": "`vernacular_creative_group` `vernacular0_`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 4369543,
                    "cost": 961948
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "content_id",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ]
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`vernacular_creative_group` `vernacular0_`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "creative_template_id",
                      "rows": 2.18e6,
                      "cost": 701065,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 80,
                "rows_for_plan": 1.75e6,
                "cost_for_plan": 701065,
                "chosen": true
              }
            ]
          }
}