How can my application benefit from temporary tables?

Question

I've been reading a little about temporary tables in MySQL but I'm an admitted newbie when it comes to databases in general and MySQL in particular. I've looked at some examples and the MySQL documentation on how to create a temporary table, but I'm trying to determine just how temporary tables might benefit my applications and I guess secondly what sorts of issues I can run into. Granted, each situation is different, but I guess what I'm looking for is some general advice on the topic.

I did a little googling but didn't find exactly what I was looking for on the topic. If you have any experience with this, I'd love to hear about it.

Thanks, Matt

Answer 1

Temporary tables are often valuable when you have a fairly complicated SELECT you want to perform and then perform a bunch of queries on that...

You can do something like:

CREATE TEMPORARY TABLE myTopCustomers
   SELECT customers.*,count(*) num from customers join purchases using(customerID)
   join items using(itemID) GROUP BY customers.ID HAVING num > 10;

And then do a bunch of queries against myTopCustomers without having to do the joins to purchases and items on each query. Then when your application no longer needs the database handle, no cleanup needs to be done.

Almost always you'll see temporary tables used for derived tables that were expensive to create.