MySQL Temporary Tables and Rails

Note: The RichText blog has moved to www.ricroberts.com

According to the Rails documentation, all active record classes by default use the same database connection.

As the Agile Web Development With Rails book rightly says, Ruby on rails is not thread safe (2nd Edtn p 649). A Mongrel server uses one thread per request (i.e. multithreaded), but is synchronised around the calls to Dispatcher.dispatch. This means that everything is threaded right before and right after Rails runs. While Rails is running there is only one controller in operation at a time. This is why people typically have to run a small set of Mongrel processes (a “Pack of Mongrels”) to get good concurrency (from Mongrel FAQs).

MySQL temporary tables are only visible inside the connection from which they are created, and they are automatically dropped when the connection is dropped (see the MySQL docs). Along with the above, this means that any rails active record class will be able to see temporary tables created by other active record classes (within the same server process).

However, since only one controller operation is running at a time within each server process (and therefore only one for each connection), I think you can safely do the following in a rails active record model class if you want to use temporary tables:

begin
  connection.execute("DROP TEMPORARY TABLE IF EXISTS my_temp_table")
  connection.execute("CREATE TEMPORARY TABLE my_temp_table")
  # do stuff with temp table here
ensure
  # this drop is here to help keep the size of the data base down 
  # between calls to this method
  connection.execute("DROP TEMPORARY TABLE IF EXISTS my_temp_table")
end