Optimizing SELECT Statements SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( ’a’, ’b’ ), ( ’c’, ’d’ )); SELECT ... FROM t1 WHERE ( col_1 = ’a’ AND col_2 = ’b’ ) OR ( col_1 = ’c’ AND col_2 = ’d’ ); Previously, for range scans to be used, it was necessary to write the query as: For the optimizer to use a range scan, queries must satisfy these conditions: •Only  IN()  predicates are used, not  NOT IN() . •On the left side of the  IN()  predicate, the row constructor contains only column references. •On the right side of the  IN()  predicate, row constructors contain only runtime constants, which are either literals or local column references that are bound to constants during execution. •On the right side of the  IN()  predicate, there is more than one row constructor. For more information about the optimizer and row constructors, see Section 8.2.1.22, “Row Constructor Expression Optimization” Limiting Memory Use for Range Optimization To control the memory available to the range optimizer, use the  range_optimizer_max_mem_size system variable: •A value of 0 means “no limit.” •With a value greater than 0, the optimizer tracks the memory consumed when considering the range access method. If the specified limit is about to be exceeded, the range access method is abandoned and other methods, including a full table scan, are considered instead. This could be less optimal. If this happens, the following warning occurs (where  N  is the current  range_optimizer_max_mem_size value): Warning 3170 Memory capacity of  N  bytes for ’range_optimizer_max_mem_size’ exceeded. Range optimization was not done for this query. •For  UPDATE  and  DELETE  statements, if the optimizer falls back to a full table scan and the sql_safe_updates  system variable is enabled, an error occurs rather than a warning because, in effect, no key is used to determine which rows to modify. For more information, see Using Safe-Updates Mode (--safe-updates). For individual queries that exceed the available range optimization memory and for which the optimizer falls back to less optimal plans, increasing the  range_optimizer_max_mem_size  value may improve performance. To estimate the amount of memory needed to process a range expression, use these guidelines: •For a simple query such as the following, where there is one candidate key for the range access method, each predicate combined with  OR  uses approximately 230 bytes: SELECT COUNT(*) FROM t WHERE a=1 OR a=2 OR a=3 OR .. . a= N ; SELECT COUNT(*) FROM t •Similarly for a query such as the following, each predicate combined with  AND  uses approximately 125 bytes: 1650