mysql> SELECT @@optimizer_switch LIKE ’%prefer_ordering_index=on%’; +------------------------------------------------------+ | @@optimizer_switch LIKE ’%prefer_ordering_index=on%’ | +------------------------------------------------------+ | 1 | +------------------------------------------------------+ 

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. 

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 

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: 

## 8.2.1.20 Function Call Optimization 

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 

See also Section 8.9.2, “Switchable Optimizations” . 

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. 

