How MySQL executes queries is one of the major factors of database performance. You can verify the MySQL execution plan using the EXPLAIN
command. Starting from MySQL 5.7.2, you can use EXPLAIN
to examine queries currently executing in other sessions. EXPLAIN FORMAT=JSON
gives detailed information.
Let's get into the details.
The explain plan gives information on how the optimizer is going to execute the query. You just need to prefix the EXPLAIN
keyword to the query:
mysql> EXPLAIN SELECT dept_name FROM dept_emp JOIN employees ON dept_emp.emp_no=employees.emp_no JOIN departments ON departments.dept_no=dept_emp.dept_no WHERE employees.first_name='Aamer'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: ref possible_keys: PRIMARY,name key: name key_len: 58 ref: const rows: 228 filtered: 100.00...