SELECT * FROM orders WHERE amount BETWEEN 100.0 AND 300.0; SELECT * FROM tbl WHERE col1 = 15 AND col2 > 100; 

The requirement for comparison against a constant value includes functions that are constant, such as ABS()  and FLOOR() : 

SELECT * FROM tbl WHERE col1 < ABS(-34); 

Histogram statistics are useful primarily for nonindexed columns. Adding an index to a column for which histogram statistics are applicable might also help the optimizer make row estimates. The tradeoffs are: 

•An index must be updated when table data is modified. 

•A histogram is created or updated only on demand, so it adds no overhead when table data is modified. On the other hand, the statistics become progressively more out of date when table modifications occur, until the next time they are updated. 

The optimizer prefers range optimizer row estimates to those obtained from histogram statistics. If the optimizer determines that the range optimizer applies, it does not use histogram statistics. 

For columns that are indexed, row estimates can be obtained for equality comparisons using index dives (see Section 8.2.1.2, “Range Optimization” ). In this case, histogram statistics are not necessarily useful because index dives can yield better estimates. 

In some cases, use of histogram statistics may not improve query execution (for example, if the statistics are out of date). To check whether this is the case, use ANALYZE TABLE  to regenerate the histogram statistics, then run the query again. 

Alternatively, to disable histogram statistics, use ANALYZE TABLE  to drop them. A different method of disabling histogram statistics is to turn off the condition_fanout_filter  flag of the optimizer_switch  system variable (although this may disable other optimizations as well): 

SET optimizer_switch=’condition_fanout_filter=off’; 

If histogram statistics are used, the resulting effect is visible using EXPLAIN . Consider the following query, where no index is available for column col1 : 

SELECT * FROM t1 WHERE col1 < 24; 

If histogram statistics indicate that 57% of the rows in t1  satisfy the col1 < 24  predicate, filtering can occur even in the absence of an index, and EXPLAIN  shows 57.00 in the filtered  column. 

## 8.10 Buffering and Caching 

MySQL uses several strategies that cache information in memory buffers to increase performance. 

## 8.10.1 InnoDB Buffer Pool Optimization 

InnoDB  maintains a storage area called the buffer pool for caching data and indexes in memory. Knowing how the InnoDB  buffer pool works, and taking advantage of it to keep frequently accessed data in memory, is an important aspect of MySQL tuning. 

For an explanation of the inner workings of the InnoDB  buffer pool, an overview of its LRU replacement algorithm, and general configuration information, see Section 15.5.1, “Buffer Pool” . 

For additional InnoDB  buffer pool configuration and tuning information, see these sections: 

•Section 15.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)” 

