Optimizing SELECT Statements Nondeterministic functions may affect query performance. For example, some optimizations may not be available, or more locking might be required. The following discussion uses  RAND()  but applies to other nondeterministic functions as well. Suppose that a table  t  has this definition: CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100)); Consider these two queries: SELECT * FROM t WHERE id = POW(1,2); SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49); Both queries appear to use a primary key lookup because of the equality comparison against the primary key, but that is true only for the first of them: The effects of nondeterminism are not limited to  SELECT  statements. This  UPDATE  statement uses a nondeterministic function to select rows to be modified: •The first query always produces a maximum of one row because  POW()  with constant arguments is a constant value and is used for index lookup. •The second query contains an expression that uses the nondeterministic function  RAND() , which is not constant in the query but in fact has a new value for every row of table  t . Consequently, the query reads every row of the table, evaluates the predicate for each row, and outputs all rows for which the primary key matches the random value. This might be zero, one, or multiple rows, depending on the  id  column values and the values in the  RAND()  sequence. UPDATE t SET col_a =  some_expr  WHERE id = FLOOR(1 + RAND() * 49); Presumably the intent is to update at most a single row for which the primary key matches the expression. However, it might update zero, one, or multiple rows, depending on the  id  column values and the values in the  RAND()  sequence. The behavior just described has implications for performance and replication: The difficulties stem from the fact that the  RAND()  function is evaluated once for every row of the table. To avoid multiple function evaluations, use one of these techniques: •Because a nondeterministic function does not produce a constant value, the optimizer cannot use strategies that might otherwise be applicable, such as index lookups. The result may be a table scan. • InnoDB  might escalate to a range-key lock rather than taking a single row lock for one matching row. •Updates that do not execute deterministically are unsafe for replication. •Move the expression containing the nondeterministic function to a separate statement, saving the value in a variable. In the original statement, replace the expression with a reference to the variable, which the optimizer can treat as a constant value: •Assign the random value to a variable in a derived table. This technique causes the variable to be assigned a value, once, prior to its use in the comparison in the  WHERE  clause: SET @keyval = FLOOR(1 + RAND() * 49); UPDATE t SET col_a =  some_expr  WHERE id = @keyval; UPDATE /*+ NO_MERGE(dt) */ t, (SELECT FLOOR(1 + RAND() * 49) AS r) AS dt SET col_a =  some_expr  WHERE id = dt.r; 1690