mysql [localhost] {msandbox} (test2) > select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT SQL_CALC_FOUND_ROWS     u.*,     (SELECT created_at FROM activity_log WHERE user_id = u.id ORDER BY id DESC LIMIT 1) AS activity_at FROM     users AS u ORDER BY     activity_at DESC LIMIT 0, 5
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select `activity_log`.`created_at` from `activity_log` where (`activity_log`.`user_id` = `u`.`id`) order by `activity_log`.`id` desc limit 1"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select sql_calc_found_rows `u`.`id` AS `id`,`u`.`username` AS `username`,`u`.`created_at` AS `created_at`,(/* select#2 */ select `activity_log`.`created_at` from `activity_log` where (`activity_log`.`user_id` = `u`.`id`) order by `activity_log`.`id` desc limit 1) AS `activity_at` from `users` `u` order by `activity_at` desc limit 0,5"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`users` `u`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`users` `u`",
                "table_scan": {
                  "rows": 8083,
                  "cost": 22
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`users` `u`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "rows": 8083,
                      "cost": 1638.6,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 1638.6,
                "rows_for_plan": 8083,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`users` `u`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`activity_at` desc",
              "items": [
                {
                  "item": "(/* select#2 */ select `activity_log`.`created_at` from `activity_log` where (`activity_log`.`user_id` = `u`.`id`) order by `activity_log`.`id` desc limit 1)"
                }
              ] /* items */,
              "resulting_clause_is_simple": false,
              "resulting_clause": "`activity_at` desc"
            } /* clause_processing */
          },
          {
            "refine_plan": [
              {
                "table": "`users` `u`",
                "access_type": "table_scan"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 114,
                "key_length": 0,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 147168
              } /* tmp_table_info */
            } /* creating_tmp_table */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_optimization": {
                    "select#": 2,
                    "steps": [
                      {
                        "condition_processing": {
                          "condition": "WHERE",
                          "original_condition": "(`activity_log`.`user_id` = `u`.`id`)",
                          "steps": [
                            {
                              "transformation": "equality_propagation",
                              "resulting_condition": "(`activity_log`.`user_id` = `u`.`id`)"
                            },
                            {
                              "transformation": "constant_propagation",
                              "resulting_condition": "(`activity_log`.`user_id` = `u`.`id`)"
                            },
                            {
                              "transformation": "trivial_condition_removal",
                              "resulting_condition": "(`activity_log`.`user_id` = `u`.`id`)"
                            }
                          ] /* steps */
                        } /* condition_processing */
                      },
                      {
                        "table_dependencies": [
                          {
                            "table": "`activity_log`",
                            "row_may_be_null": false,
                            "map_bit": 0,
                            "depends_on_map_bits": [
                            ] /* depends_on_map_bits */
                          }
                        ] /* table_dependencies */
                      },
                      {
                        "ref_optimizer_key_uses": [
                          {
                            "table": "`activity_log`",
                            "field": "user_id",
                            "equals": "`u`.`id`",
                            "null_rejecting": false
                          }
                        ] /* ref_optimizer_key_uses */
                      },
                      {
                        "rows_estimation": [
                          {
                            "table": "`activity_log`",
                            "table_scan": {
                              "rows": 104447,
                              "cost": 225
                            } /* table_scan */
                          }
                        ] /* rows_estimation */
                      },
                      {
                        "considered_execution_plans": [
                          {
                            "plan_prefix": [
                            ] /* plan_prefix */,
                            "table": "`activity_log`",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "ref",
                                  "index": "user_id",
                                  "rows": 1068,
                                  "cost": 888.6,
                                  "chosen": true <---- Initially chosen
                                },
                                {
                                  "access_type": "scan",
                                  "rows": 78336,
                                  "cost": 21114,
                                  "chosen": false
                                }
                              ] /* considered_access_paths */
                            } /* best_access_path */,
                            "cost_for_plan": 888.6,
                            "rows_for_plan": 1068,
                            "chosen": true
                          }
                        ] /* considered_execution_plans */
                      },
                      {
                        "attaching_conditions_to_tables": {
                          "original_condition": "(`activity_log`.`user_id` = `u`.`id`)",
                          "attached_conditions_computation": [
                          ] /* attached_conditions_computation */,
                          "attached_conditions_summary": [
                            {
                              "table": "`activity_log`",
                              "attached": null
                            }
                          ] /* attached_conditions_summary */
                        } /* attaching_conditions_to_tables */
                      },
                      {
                        "clause_processing": {
                          "clause": "ORDER BY",
                          "original_clause": "`activity_log`.`id` desc",
                          "items": [
                            {
                              "item": "`activity_log`.`id`"
                            }
                          ] /* items */,
                          "resulting_clause_is_simple": true,
                          "resulting_clause": "`activity_log`.`id` desc"
                        } /* clause_processing */
                      },
                      {
                        "refine_plan": [
                          {
                            "table": "`activity_log`"
                          }
                        ] /* refine_plan */
                      },
                      {
                        "added_back_ref_condition": "((`activity_log`.`user_id` <=> `u`.`id`))"
                      },
                      {
                        "reconsidering_access_paths_for_index_ordering": {
                          "clause": "ORDER BY",
                          "index_order_summary": {
                            "table": "`activity_log`",
                            "index_provides_order": true,
                            "order_direction": "desc",
                            "index": "PRIMARY",
                            "plan_changed": true,  <---- Plan changed
                            "access_type": "index_scan"
                          } /* index_order_summary */
                        } /* reconsidering_access_paths_for_index_ordering */
                      }
                    ] /* steps */
                  } /* join_optimization */
                },
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 3247476
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)