• <cache>( expr ) 

The expression (such as a scalar subquery) is executed once and the resulting value is saved in memory for later use. For results consisting of multiple values, a temporary table may be created and <temporary table>  is shown instead. 

• <exists>( query fragment ) 

The subquery predicate is converted to an EXISTS  predicate and the subquery is transformed so that it can be used together with the EXISTS  predicate. 

• <in_optimizer>( query fragment ) 

This is an internal optimizer object with no user significance. 

• <index_lookup>( query fragment ) 

The query fragment is processed using an index lookup to find qualifying rows. 

• <if>( condition , expr1 , expr2 ) 

If the condition is true, evaluate to expr1 , otherwise expr2 . 

• <is_not_null_test>( expr ) 

A test to verify that the expression does not evaluate to NULL . 

• <materialize>( query fragment ) 

Subquery materialization is used. 

• ‘materialized-subquery‘. col_name 

A reference to the column col_name  in an internal temporary table materialized to hold the result from evaluating a subquery. 

• <primary_index_lookup>( query fragment ) 

The query fragment is processed using a primary key lookup to find qualifying rows. 

• <ref_null_helper>( expr ) 

This is an internal optimizer object with no user significance. 

• /* select# N  */ select_stmt 

The SELECT  is associated with the row in non-extended EXPLAIN  output that has an id  value of N . 

• outer_tables  semi join ( inner_tables ) 

A semijoin operation. inner_tables  shows the tables that were not pulled out. See Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations” . 

• <temporary table> 

This represents an internal temporary table created to cache an intermediate result. 

When some tables are of const  or system  type, expressions involving columns from these tables are evaluated early by the optimizer and are not part of the displayed statement. However, with FORMAT=JSON , some const  table accesses are displayed as a ref  access that uses a const value. 

