All this code is being placed inside a stored procedure which will create my temp table with all the data that I will need to use in my application. There are many more columns involved that belong to many different tables. I just didn't want to mention it for less confusion. In short, I need it in a temp table for better convenience for me and less strain on my app.
by the way, creating a temp table is one of the most expensive things you can do -- it's more strain on your app, not less
Then I guess sorry you're confused?
I think you'll find that most db developers use temp tables, especially when working with stored procedures. They can improve your code's performance and maintain ability if used correctly. But let's get back on track shall we?
Then I am curious. So you are saying that most db developers are wrong for using temporary tables? Are there not any situations where they are useful? I just find that hard to believe. This link talks about temp tables and their usefulness. So you're saying this is bad practice? I just want to make this clear.
Are there not any situations where they are useful?
Well, this is 180 degrees in the other direction<g>.
As with so many "things" there is no one right way for every situation. What is happening way to much is that developers are looking for an "easy way" that will usually require less thought/work, but may perform well.
What continues to happen with db development is that once a "way" is found, it is propogated as much as possible. It reminds me of when i was working on our houses and my children all wanted to help when they were quite small. First thing i let them do was to pull some nails from wood with a claw hammer. Then i showed them how to pound a small nail into wood and then to nail 2 small pieces of wood together. This took some weeks or months depending on how much we were "working on". Of course, they decided that every "to do" around the house was a nail, and they were cool with the hammer. The 2 that continued to be interested became quite handy with all of the common tools.
As (db) developers grow, they will usually learn there are multiple "good" ways to address an issue.
Temporary tables can give performance boosts ONLY if used in the right situation. Unfortunately there many developers that use temporary tables for all situations whether they are applicable or not.
If you look at what goes on during a temporary table creation you will see that once you exceed a certain threshold of data the data gets put into MyISAM tables (INSERTS) which can be expensive. If there are indexes associated with the temporary table then this too will create more I/O and slow down the overall app. However, on the flip side if the temporary table and its contents are going to be accessed many times then it might be better to have them in the end. The definition of many times is open to interpretation but I would say 100's of queries against a temporary table.
There are other ways of organizing the data in the database tables using partitioninig with each partition viewed as its own separate table. If you use a good partitioning scheme this might result in better performance without the need of temporary tables.
Finally if you are developing an application that there are MySQL parameters which provide a connection pool of connections to the database. Rather than closing the connection and reopening it this remains open - the result of this is that the temporary table are not removed as some people believe and they keep adding to the temporary table. This can lead to misleading results.