Optimizing SELECT Statements mysql>  SELECT @@optimizer_switch LIKE ’%prefer_ordering_index=on%’; +------------------------------------------------------+ | @@optimizer_switch LIKE ’%prefer_ordering_index=on%’ | +------------------------------------------------------+ | 1 | +------------------------------------------------------+ mysql>  EXPLAIN SELECT c2 FROM t -> WHERE id2 > 3 -> ORDER BY id1 ASC LIMIT 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: index possible_keys: i key: PRIMARY key_len: 8 ref: NULL rows: 2 filtered: 70.00 Extra: Using where mysql>  SET optimizer_switch = "prefer_ordering_index=off"; mysql>  EXPLAIN SELECT c2 FROM t -> WHERE id2 > 3 -> ORDER BY id1 ASC LIMIT 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: range possible_keys: i key: i key_len: 8 ref: NULL rows: 14 filtered: 100.00 Extra: Using index condition; Using filesort MySQL functions are tagged internally as deterministic or nondeterministic. A function is nondeterministic if, given fixed values for its arguments, it can return different results for different invocations. Examples of nondeterministic functions:  RAND() ,  UUID() . If a function is tagged nondeterministic, a reference to it in a  WHERE  clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join). MySQL also determines when to evaluate functions based on types of arguments, whether the arguments are table columns or constant values. A deterministic function that takes a table column as argument must be evaluated whenever that column changes value. See also Section 8.9.2, “Switchable Optimizations” . 1689 8.2.1.20 Function Call Optimization Since the following query has a  LIMIT  clause, we expect it to use an ordered index, if possible. In this case, as we can see from the  EXPLAIN  output, it uses the table’s primary key. Now we disable the  prefer_ordering_index  flag, and re-run the same query; this time it uses the index  i  (which includes the  id2  column used in the  WHERE  clause), and a filesort: