How does MySQL internally optimize and cache results of deterministic functions?

During query parsing, MySQL evaluates deterministic functions whose inputs are constants.

It replaces them with their computed literal values (constant folding).

This reduces repeated computation at runtime.

MySQL may compute a deterministic function once per row or once per statement depending on the context.

When the same expression appears multiple times in a query, MySQL avoids recomputing it.

Cached values may be reused within the same SELECT, JOIN, or WHERE clause.

User-defined stored functions can be declared DETERMINISTIC.

This allows the optimizer to apply constant folding and expression caching.

Without declaring DETERMINISTIC, MySQL assumes the function might change results and avoids optimizations.

Caching is only valid within the scope of a query—not across multiple queries.

MySQL does not maintain a global cache of function results.

Non-deterministic functions are never cached because doing so would break correctness.