MAX_EXECUTION_TIME hint and SELECT statement termination granularity

Answer to the Core Questions

1. Granularity across storage engines

• InnoDB – The engine checks elapsed time after each block of rows is fetched from the buffer pool. The block size is normally 4 KB, but it can grow for large scans or when the optimizer chooses a clustered index scan. Checks happen roughly every few milliseconds, but a long index scan may delay a check until the scan completes.
• MyISAM – MyISAM performs a timeout check after each row is read from the data file. Because the engine streams rows directly, the granularity is finer (typically <1 ms between rows) and the hint is usually respected more closely.
• NDB/Cluster – In a MySQL Cluster setup, the timeout is enforced after each batch of rows returned from a data node. The batch size is controlled by the node’s internal buffer (default 16 KB), so the check frequency is coarser than MyISAM but finer than InnoDB’s large scans.
• Temporary tables / filesorts – When a query materializes a temporary table or performs a filesort, the timeout is not checked until the materialization step finishes. This can introduce a large gap between the requested limit and the actual termination time.
• Lock waits & latches – If the statement is blocked on a lock or a metadata latch, the server does not perform a timeout check until the wait ends. The query can therefore exceed the limit by the duration of the wait.

2. Conditions that allow a SELECT to exceed the defined millisecond limit before error 3024

• Queries that generate a large temporary table or filesort (e.g., SELECT COUNT(*) FROM (SELECT * FROM big_table) AS t) – the hint is only evaluated after the temporary structure is fully built.
• Aggregations or subqueries that do not emit rows until the final result is computed (e.g., SELECT SUM(col) FROM table) – the timeout checkpoint is reached only after the aggregation finishes.
• Operations that involve a lock wait or a long metadata latch – the hint is ignored until the wait ends.
• Certain optimizer choices that bypass row‑fetch checkpoints, such as UNION with DISTINCT or subqueries in the FROM clause that force early materialization.

Practical Verification Steps for Your Environment

1. Create a test table for each engine:

   CREATE TABLE t_innodb (id INT PRIMARY KEY, val INT) ENGINE=InnoDB;
   CREATE TABLE t_myisam (id INT PRIMARY KEY, val INT) ENGINE=MyISAM;
   -- (If you have NDB, create a similar table with ENGINE=NDB)

2. Populate with data:

   INSERT INTO t_innodb SELECT n, n FROM seq_1_to_1000000;  -- adjust size
   INSERT INTO t_myisam SELECT n, n FROM seq_1_to_1000000;

3. Run a hinted SELECT and monitor actual duration:

   SET SESSION performance_schema = ON;
   SELECT * FROM t_innodb WHERE id > 500000 WITH MAX_EXECUTION_TIME(100);
   -- Check performance_schema.events_statements_current for elapsed_time

4. Test a temporary‑table scenario:

   SELECT COUNT(*) FROM (SELECT * FROM t_innodb) AS sub WITH MAX_EXECUTION_TIME(200);

5. Induce a lock wait:

   -- Session A
   START TRANSACTION;
   UPDATE t_innodb SET val = val + 1 WHERE id = 1;
   -- Session B (immediately)
   SELECT * FROM t_innodb WITH MAX_EXECUTION_TIME(50);  -- will wait until the lock is released

Observe that in the first two tests the query terminates close to the specified limit, while in the temporary‑table and lock‑wait tests the actual elapsed time can exceed the hint value by a noticeable margin before error 3024 is raised.

What to Check If You Observe Unexpected Overruns

Verify whether your query triggers a temporary table, filesort, or lock wait. If so, the hint’s granularity will be coarser, and you may need to adjust the limit or rewrite the query to reduce materialization.

Ask for Missing Diagnostic Detail

To tailor the recommendation further, could you provide the specific query that is exceeding the timeout? Knowing whether it involves aggregation, subqueries, or locks will help refine the guidance.