Comments on: Introduction to MySQL 8.0 Common Table Expressions (Part 1)

By: Corrado Pandiani

Corrado Pandiani — Thu, 13 Feb 2020 19:57:17 +0000

In reply to Øystein Grøvlen. Hi, you're right. It's the same as the view. Even for CTE the optimizer can merge the CTE code or materialize it. The aim of the article was to show the usage and the benefit of CTE by the application perspective. Anyway it is worth considering to write anotehr article discussing about internals and the optimizer optimization when using CTE. Thanks

By: Øystein Grøvlen

Øystein Grøvlen — Thu, 13 Feb 2020 18:46:31 +0000

Note that a CTE is not always materialized. Like a derived table, it may be merged into the main query if it does not contain aggregation, limit, union etc.

By: Corrado Pandiani

Corrado Pandiani — Tue, 11 Feb 2020 14:49:49 +0000

In reply to Adnan. Hi Adnan. Basically the execution plan of a view and a CTE is the same if the derived query is referenced only once. The CTE will be significantly better than a view if the derived query is referenced more times. The CTE materialize the query at the beginning anche than can be referenced as many times you need. For the view is different. Any sinle reference of a derived table requires materialization. This involves more memiry usage and worse performance. I hope this helps.

By: Adnan

Adnan — Mon, 10 Feb 2020 20:08:04 +0000

So can we take CTE as an alternative to View ? and what about memory efficiency in CTE ? is it efficient than View or standard sub queries ? if so, then it will give a big benefit on performance optimization.