This is more of a "please confirm/correct my understanding" question.
Background I connect to MySQL using JDBC, the connection pool has around 250 connections, most of which seem persistently connected even after query termination - all use the same User account. The Stored Procedure that I wish to use a Temporary Table for is called by Java code and performs a transform on a JSON column to convert that into a row for a different table. The transform is relatively expensive, performed over potentially thousands of rows, so I don't wish to perform the transform more than once. To get around this, my proposed solution is to transform the data into a temporary table, perform my statistical queries around the now transformed data, and then insert this wholesale into the target table.
Question I have read that Temporary tables are bound to the User. Am I right in theorising that because the connections are seemingly never terminated and all use the same user account, that the temporary table created in the procedure would be shared across all connections and queries (and so the data could be accessed by other connections running the same procedure at the same time)?
Limitations What I would like to have done is either transmit the original data, or perform the transform and transmit that data back to Java. However, we're dealing with around a few hundred megabytes of data each time and the data transfer back to Java seems to lock the database until it's finished the transmission. (I'm not sure why, if anyone has a recommendation to fix this, please let me know)
So I'm left with the only other solution I could think of which is to not transmit the source data, but to perform the transform, collect the aggregate data I need, then insert the transformed data to the target without it ever leaving the database server.
Thanks in advance