This is done with
SET optimizer_trace_offset=<OFFSET>, optimizer_trace_limit=<LIMIT>
where OFFSET is a signed integer, and LIMIT is a positive integer. The default for optimizer_trace_offset is -1; the default for optimizer_trace_limit is 1. The SET statement has the following effects:
All remembered traces are cleared
A later SELECT on the
OPTIMIZER_TRACEtable returns the first LIMIT traces of the OFFSET oldest remembered traces (if OFFSET ≥ 0), or the first LIMIT
traces of the (-OFFSET) newest remembered traces (if OFFSET < 0).
For example, a combination of OFFSET=-1 and LIMIT=1 will make the last trace be shown (as is default), OFFSET=-2 and LIMIT=1 will make the next-to-last be shown, OFFSET=-5 and LIMIT=5 will make the last five traces be shown. Such negative OFFSET can be useful when one knows that the interesting substatements are the few last ones of a stored routine, like this:
SET optimizer_trace_offset=-5, optimizer_trace_limit=5; CALL stored_routine(); # more than 5 substatements in this routine SELECT * FROM information_schema.OPTIMIZER_TRACE; # see only last 5 traces
On the opposite, a positive OFFSET can be useful when one knows that the interesting substatements are the few first ones of a stored routine.
The more accurately those two variables are adjusted, the less memory is used. For example, OFFSET=0 and LIMIT=5 will use memory to remember 5 traces, so if only the three first are needed, OFFSET=0 and LIMIT=3 is better (tracing stops after LIMIT traces, so the 4th and 5th trace are not created and take up no memory). A stored routine may have a loop which executes many substatements and thus generates many traces, which would use a lot of memory; proper OFFSET and LIMIT can restrict tracing to one iteration of the loop for example. This also gains speed, as tracing a substatement impacts performance.
If OFFSET≥0, only LIMIT traces are kept in memory. If OFFSET<0, that is not true: instead, (-OFFSET) traces are kept in memory; indeed even if LIMIT is smaller than (-OFFSET), so excludes the last statement, the last statement must still be traced because it will be inside LIMIT after executing one more statement (remember than OFFSET<0 is counted from the end: the "window" slides as more statements execute).
Such memory and speed gains are the reason why optimizer_trace_offset and optimizer_trace_limit, which are restrictions at the trace producer level, are offered. They are better than using
SELECT * FROM OPTIMIZER_TRACE LIMIT <LIMIT> OFFSET <OFFSET>;
which is a restriction on the trace consumer level and saves almost nothing.