The operations and properties in MySQL do not have a significant difference in formats, and are defined together in the source code.
We referred to the official documentation and the Workbench to understand the semantics and distinguish the operations and properties.
Most operations are defined as the keys in the JSON format of query plans except for the operations of the Read category, which are showed as the values of the key access_type.
| Operation | Category | Reference | Description |
|---|---|---|---|
| ALL | Producer | Link | A full table scan. |
| attached_subqueries | Producer | Link | Scan data from subqueries. |
| buffer_result | Executor | Link | Cache the data into the buffer. |
| const | Producer | Link | The table has only one row for the scan. |
| duplicates_removal | Combinator | Link | Remove duplicate data. |
| eq_ref | Producer | Link | Scan the table with the index and return one row only if joining with another table. |
| fulltext | Producer | Link | Scan the table with the full-text index. |
| grouping_operation | Folder | Link | Group data. |
| having_subqueries | Producer | Link | Read the subquery from the having clause. |
| index | Producer | Link | It tries an index-only scan and executes a full table scan if the index contains all required data. |
| index_merge | Join | Link | The Index Merge access method retrieves rows with multiple range scans and merges their results into one. |
| index_subquery | Producer | Link | Scan the data with indexes, but replaces the list in IN clause with a subquery. |
| materialized_from_subquery | Executor | Link | Materialize data from associated subqueries. |
| nested_loop | Join | Link | Performs a nested loop join. |
| ordering_operation | Combinator | Link | Sort data by specific keys. |
| range | Producer | Link | Scan the table in a range. |
| ref | Producer | Link | All rows with matching index values are read from this table |
| ref_or_null | Producer | Link | Similar to eq_ref, but does an extra search for rows that contain NULL values. |
| select_list_subqueries | Producer | Link | Scan data from subqueries in the SELECT clause. |
| system | Producer | Link | The table has only one row (= system table). |
| table | Producer | Link | Read data from a table. |
| union_result | Combinator | Link | Union results. |
| unique_subquery | Producer | Link | Scan the data with unique indexes, but replaces the list in IN clause with a subquery. |