Optimizing SELECT Statements WHERE zipcode=’95054’ AND lastname LIKE ’%etrunia%’ AND address LIKE ’%Main Street%’; MySQL can use the index to scan through people with  zipcode=’95054’ . The second part ( lastname LIKE ’%etrunia%’ ) cannot be used to limit the number of rows that must be scanned, so without Index Condition Pushdown, this query must retrieve full table rows for all people who have  zipcode=’95054’ . With Index Condition Pushdown, MySQL checks the  lastname LIKE ’%etrunia%’  part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the  zipcode condition but not the  lastname  condition. Index Condition Pushdown is enabled by default. It can be controlled with the  optimizer_switch  system variable by setting the  index_condition_pushdown  flag: SET optimizer_switch = ’index_condition_pushdown=off’; SET optimizer_switch = ’index_condition_pushdown=on’; See Section 8.9.2, “Switchable Optimizations” . 8.2.1.7 Nested-Loop Join Algorithms MySQL executes joins between tables using a nested-loop algorithm or variations on it. •Nested-Loop Join Algorithm •Block Nested-Loop Join Algorithm Nested-Loop Join Algorithm A simple nested-loop join (NLJ) algorithm reads rows from the first table in a loop one at a time, passing each row to a nested loop that processes the next table in the join. This process is repeated as many times as there remain tables to be joined. Assume that a join between three tables  t1 ,  t2 , and  t3  is to be executed using the following join types: Table Join Type t1 range t2 ref t3 ALL If a simple NLJ algorithm is used, the join is processed like this: for each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } } Because the NLJ algorithm passes rows one at a time from outer loops to inner loops, it typically reads tables processed in the inner loops many times. A Block Nested-Loop (BNL) join algorithm uses buffering of rows read in outer loops to reduce the number of times that tables in inner loops must be read. For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the buffer. This reduces by an order of magnitude the number of times the inner table must be read. 1660 Block Nested-Loop Join Algorithm