Optimizing SELECT Statements In all of the cases in the preceding list, it is possible for the condition to be converted into the form of one or more direct comparisons between a column and a constant. Engine condition pushdown is enabled by default. To disable it at server startup, set the optimizer_switch  system variable’s  engine_condition_pushdown  flag to  off . For example, in a my.cnf  file, use these lines: [mysqld] optimizer_switch=engine_condition_pushdown=off At runtime, disable condition pushdown like this: SET optimizer_switch=’engine_condition_pushdown=off’; Limitations.   Engine condition pushdown is subject to the following limitations: Previously, engine condition pushdown was limited to terms referring to column values from the same table to which the condition was being pushed. Beginning with NDB 8.0.16, column values from tables earlier in the query plan can also be referred to from pushed conditions. This reduces the number of rows which must be handled by the SQL node during join processing. Filtering can be also performed in parallel in the LDM threads, rather than in a single  mysqld  process. This has the potential to improve performance of queries by a significant margin. Beginning with NDB 8.0.20, an outer join using a scan can be pushed if there are no unpushable conditions on any table used in the same join nest, or on any table in join nmests above it on which it depends. This is also true for a semijoin, provided the optimization strategy employed is  firstMatch  (see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations” ). Join algorithms cannot be combined with referring columns from previous tables in the following two situations: •Joins are not directly supported; conditions involving multiple tables are pushed separately where possible. Use extended  EXPLAIN  output to determine which conditions are actually pushed down. See Section 8.8.3, “Extended EXPLAIN Output Format” . •A string value to be compared with a column must use the same collation as the column. •Columns used in comparisons cannot be of any of the  BLOB  or  TEXT  types. This exclusion extends to JSON ,  BIT , and  ENUM  columns as well. •Prior to NDB 8.0.18, columns could be compared with constants or expressions which evaluate to constant values only. In NDB 8.0.18 and later, columns can be compared with one another as long as they are of exactly the same type, including the same signedness, length, character set, precision, and scale, where these are applicable. •Engine condition pushdown is supported only by the  NDB  storage engine. 8.2.1.6 Index Condition Pushdown Optimization Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and 1658 1. When any of the referred previous tables are in a join buffer. In this case, each row retrieved from the scan-filtered table is matched against every row in the buffer. This means that there is no single specific row from which column values can be fetched from when generating the scan filter. 2. When the column originates from a child operation in a pushed join. This is because rows referenced from ancestor operations in the join have not yet been retrieved when the scan filter is generated.