MySQL Forums
Forum List  »  Newbie

creating table for temporary use (temporarily)
Posted by: Rich Bernstein
Date: November 16, 2022 03:59PM

I am using Koolreport in my php program to build tables for display. They are to be dropped right after the user generates the report. My original plan was to use TEMPORARY tables since they automatically get dropped as soon as the user disconnects. That was the plan and I was hoping the system speed would be nicely affected by the use of RAM.

BUT, turns out that PHP and mysql TEMPORARY tables don't play nicely! Because of the way that PHP is constantly dropping dbase connections and then reconnecting, my TEMPORARY tables were constantly being dropped. So I "solved" this by creating real tables (about 6 for each user) and then deleting them when the user logs off. This works, but as we all know, users will often hit the X and in that case I can't drop the "temporary" tables. This causes a huge number of tables and slows the system to a crawl.

So what to do? I thought maybe I could write a small app that runs every night and drops tables with a wildcard something like "DROP *.???????substantially_all_render_temp". But I am not sure that wild cards will work in a sql statement.

Can you think of anyway of fixing this TEMPORARY TABLES limitation? I am using Mysql RDS. Would switching to Maria fix the PHP/TEMPORARY issue? How about using SQL server? or postgress?

Options: ReplyQuote

Written By
creating table for temporary use (temporarily)
November 16, 2022 03:59PM

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.