Optimizing SELECT Statements •150 rows satisfy this condition on  employee.hire_date : •1 row satisfies both conditions: employee.hire_date BETWEEN ’2018-01-01’ AND ’2018-06-01’ employee.first_name = ’John’ AND employee.hire_date BETWEEN ’2018-01-01’ AND ’2018-06-01’ Without condition filtering,  EXPLAIN  produces output like this: +----+------------+--------+------------------+---------+---------+------+----------+ | id | table | type | possible_keys | key | ref | rows | filtered | +----+------------+--------+------------------+---------+---------+------+----------+ | 1 | employee | ref | name,h_date,dept | name | const | 8 | 100.00 | | 1 | department | eq_ref | PRIMARY | PRIMARY | dept_no | 1 | 100.00 | +----+------------+--------+------------------+---------+---------+------+----------+ For  employee , the access method on the  name  index picks up the 8 rows that match a name of  ’John’ . No filtering is done ( filtered  is 100%), so all rows are prefix rows for the next table: The prefix row count is  rows  ×  filtered  = 8 × 100% = 8. With condition filtering, the optimizer additionally takes into account conditions from the  WHERE  clause not taken into account by the access method. In this case, the optimizer uses heuristics to estimate a filtering effect of 16.31% for the  BETWEEN  condition on  employee.hire_date . As a result,  EXPLAIN  produces output like this: +----+------------+--------+------------------+---------+---------+------+----------+ | id | table | type | possible_keys | key | ref | rows | filtered | +----+------------+--------+------------------+---------+---------+------+----------+ | 1 | employee | ref | name,h_date,dept | name | const | 8 | 16.31 | | 1 | department | eq_ref | PRIMARY | PRIMARY | dept_no | 1 | 100.00 | +----+------------+--------+------------------+---------+---------+------+----------+ Now the prefix row count is  rows  ×  filtered  = 8 × 16.31% = 1.3, which more closely reflects actual data set. Normally, the optimizer does not calculate the condition filtering effect (prefix row count reduction) for the last joined table because there is no next table to pass rows to. An exception occurs for  EXPLAIN : To provide more information, the filtering effect is calculated for all joined tables, including the last one. To control whether the optimizer considers additional filtering conditions, use the condition_fanout_filter  flag of the  optimizer_switch  system variable (see Section 8.9.2, “Switchable Optimizations” ). This flag is enabled by default but can be disabled to suppress condition filtering (for example, if a particular query is found to yield better performance without it). If the optimizer overestimates the effect of condition filtering, performance may be worse than if condition filtering is not used. In such cases, these techniques may help: •If a column is not indexed, index it so that the optimizer has some information about the distribution of column values and can improve its row estimates. •Similarly, if no column histogram information is available, generate a histogram (see Section 8.9.6, “Optimizer Statistics” ). •Change the join order. Ways to accomplish this include join-order optimizer hints (see Section 8.9.3, “Optimizer Hints” ),  STRAIGHT_JOIN  immediately following the  SELECT , and the  STRAIGHT_JOIN  join operator. •Disable condition filtering for the session: 1676