I don't know if it's the same for MySQL, but in Postgres a CTE would possibly force the planner to make less efficient choices [1]. Would be interesting if someone who knows more than me could clarify on this
[1] https://hakibenita.com/be-careful-with-cte-in-postgre-sql