SQL Logical Optimization

This chapter explains some key logic rewrites to help you understand how TiDB generates the final query plan. For example, when you execute the select * from t where t.a in (select t1.a from t1 where t1.b=t.b) query in TiDB, you will find that the IN sub-query t.a in (select t1.a from t1 where t1.b=t.b) does not exist because TiDB has made some rewrites here.

This chapter introduces the following key rewrites:

• Subquery Related Optimizations
• Column Pruning
• Decorrelation of Correlated Subquery
• Eliminate Max/Min
• Predicates Push Down
• Partition Pruning
• TopN and Limit Operator Push Down
• Join Reorder
• Derive TopN or Limit from Window Functions