content_image/872254.jpg
## Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions 

Each trigcond( X )  is a special function that evaluates to the following values: 

• X  when the “linked”  outer expression oe_i  is not NULL 

• TRUE  when the “linked”  outer expression oe_i  is NULL 

<img src='content_image/872254.jpg'>

AND trigcond( oe_1 = ie_1 ) AND ... AND trigcond( oe_N = ie_N ) ) 

Note 

Trigger functions are not  triggers of the kind that you create with CREATE TRIGGER . 

Equalities that are wrapped within trigcond()  functions are not first class predicates for the query optimizer. Most optimizations cannot deal with predicates that may be turned on and off at query execution time, so they assume any trigcond( X )  to be an unknown function and ignore it. Triggered equalities can be used by those optimizations: 

•Reference optimizations: trigcond( X = Y  [OR Y  IS NULL])  can be used to construct ref , eq_ref , or ref_or_null  table accesses. 

•Index lookup-based subquery execution engines: trigcond( X = Y )  can be used to construct unique_subquery  or index_subquery  accesses. 

•Table-condition generator: If the subquery is a join of several tables, the triggered condition is checked as soon as possible. 

When the optimizer uses a triggered condition to create some kind of index lookup-based access (as for the first two items of the preceding list), it must have a fallback strategy for the case when the condition is turned off. This fallback strategy is always the same: Do a full table scan. In EXPLAIN  output, the fallback shows up as Full scan on NULL key  in the Extra  column: 

mysql> EXPLAIN SELECT t1.col1, t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 ... *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: index_subquery possible_keys: key1 key: key1 key_len: 5 ref: func rows: 2 Extra: Using where; Full scan on NULL key 

If you run EXPLAIN  followed by SHOW WARNINGS , you can see the triggered condition: 

*************************** 1. row *************************** Level: Note Code: 1003 Message: select ‘test‘.‘t1‘.‘col1‘ AS ‘col1‘, <in_optimizer>(‘test‘.‘t1‘.‘col1‘, <exists>(<index_lookup>(<cache>(‘test‘.‘t1‘.‘col1‘) in t2 on key1 checking NULL 

