October 17, 2010 2:00 AM Subscribe
I think MySQL procedures and temporary tables are the bee's knees - am I Doing It Wrong?
I'm writting a bunch of reports off a MySQL database, and have settled towards the following pattern: I'll create a procedure that makes a temporary table, fills the table with data from several sources, mostly using INSERT ... SELECT and UPDATE statements, and finally selects the entire output table. The end table will usually have a few (5-10) rows, corresponding to a high level view of the data.
I could probably achieve the same effect with liberal application of views and aggregation functions, but I find the procedural approach much more readable and convenient.
Is this a Bad Thing from a performance point of view? Am I putting undue stress on the server in any way by dropping and re-creating temporary tables with each procedure invocation? Should I be using any specific options when creating the tables for best performance?