WITH (Common Table Expressions) suffer a performance penalty. If so, increasing the permitted in-memory temporary table size may improve performance; see Section 8.4.4, “Internal Temporary Table Use in MySQL” . It is important for recursive CTEs that the recursive  SELECT  part include a condition to terminate recursion. As a development technique to guard against a runaway recursive CTE, you can force termination by placing a limit on execution time: Suppose that a recursive CTE is mistakenly written with no recursion execution termination condition: By default,  cte_max_recursion_depth  has a value of 1000, causing the CTE to terminate when it recurses past 1000 levels. Applications can change the session value to adjust for their requirements: You can also set the global  cte_max_recursion_depth  value to affect all sessions that begin subsequently. For queries that execute and thus recurse slowly or in contexts for which there is reason to set the cte_max_recursion_depth  value very high, another way to guard against deep recursion is to set a per-session timeout. To do so, execute a statement like this prior to executing the CTE statement: Alternatively, include an optimizer hint within the CTE statement itself: •The  cte_max_recursion_depth  system variable enforces a limit on the number of recursion levels for CTEs. The server terminates execution of any CTE that recurses more levels than the value of this variable. •The  max_execution_time  system variable enforces an execution timeout for  SELECT  statements executed within the current session. •The  MAX_EXECUTION_TIME  optimizer hint enforces a per-query execution timeout for the  SELECT statement in which it appears. SET SESSION cte_max_recursion_depth = 10; -- permit only shallow recursion SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion SET max_execution_time = 1000; -- impose one second timeout WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte ) SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte; WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte ) WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte ) SELECT * FROM cte; Limiting Common Table Expression Recursion 2714