MySQL Forums
Forum List  »  General

CTE referencing temporary table
Posted by: Thejaka Maldeniya
Date: January 21, 2020 01:31AM

I don't believe this is mentioned in the docs, but when a CTE references a temporary table such as WITH parameters AS (SELECT * FROM parameters), it still doesn't work if multiple references are made to parameters. I was hoping to use this as a workaround for the inability to reopen temp tables, but this did not work. I had to use temporary tables, as the MySQL optimizer doesn't index temporary result sets of subqueries or CTEs, so my query wasn't performing well. With temp tables with indexes, the performance can be greatly improved. However, due to the limitation with reopening temp tables, I had to use non-temp (ordinary permanent tables), but this also has caveats, as since this set of queries is invoked based on user request to generate a report, multiple concurrent invocations are impossible. A CTE or some other solution is required as multiple references to the table are being made. Any suggestions?

I don't know where to post a feature request, but I was hoping this could somehow be fixed in a newer version of MySQL (a minor version/revision please, as I'm using on RDS and major version upgrades require downtime, and are difficult to coordinate and arrange). I know the reopening temp tables feature has already been requested, but I was hoping at least the CTE workaround could be implemented.

Thanks for your attention.

Options: ReplyQuote

Written By
CTE referencing temporary table
January 21, 2020 01:31AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.