You should log in and post some comments! The link is up there in the toolbar. Go nuts!
What are MySQL Temporary Tables?
<h3>Introduction</h3> I originally planned on writing an article over Common Table Expressions (CTEs) but since MySQL does not currently support CTEs, and there has been much debate as to why they should implement it, I decided not to write an article about it. MySQLs answer to CTEs is Temporary Tables. A temporary table is a base table that is not stored in the databse, but instead exists only while the database session in which it was created is active. In this article we will look further at what a Temporary Table is, how to use them and when it is appropriate to use them. Right off you may be asking yourself why a temporary table would ever be useful, and why wouldn't you use a view? To answer that question we must only remember that a view exists only for a single query (the table is recreated from existing data each time the name of a view is used), and a temporary table exists for the entire data base session in which it was created. In a temporary table we must add data to it using INSERT the same as a normal table, while a view is auto populated with the data retrieved by the query that calls it. The added benefit of this is that we can update data in a temporary table, even though the update is as temporary as the table it was made in. What are the cons of this process you say? Where the contents of a view are generated each time the views name is used, a view's data is almost always up to date. In a temporary table the data reflects the state of the database at the time the table was loaded with data. If the table from which the temporary table was loaded is modified, then the contents of the temporary table may become out of sync with other parts of the database. <h3>Why use a Temporary Table?</h3> Apart from the reasons described above for using a temporary table over a view, the big answer that matters to almost every DBA comes down to performance. It takes processing time to create a view, which works fine and is actually faster if you are only going to use the data once. If, however, you are going to use the data repeatedly during a session, it is better to create the structure for a temporary table as it only needs to be created once for the entire session instead of once for each call. As with all things, and as I typically state in my articles, it all comes down to use-case scenerios. There is a time and place for everything. I view repeatedly takes more time but gives always accurate data while a temporary table repetaedly saves time but runs the risk of becoming out-dated.
A great example comes back to my article on Denormalization. Say we have four tables as shown below:
This is a very normalized approach. Here we have what would be considered a large table divided up into smaller tables linked on foreigh keys. Now say we wanted to get the name of the provider a customer uses for phone service. Our SQL query might look like this:
SELECT p.provider_name from Providers
LEFT JOIN Phone_Numbers pn ON p.area_code = pn.area_code WHERE phone_number_id = (SELECT cpn.phone_number_id FROM Customers_Phone_Numbers cpn LEFT JOIN Customers c ON cpn.customer_id = c.customer_id WHERE c.customer_name = 'John Doe') Note that this code block has not been tested in a real environment, but you can quickly see how hard that might be on the eyes. Imagine if that query were to reference five tables, or six, or ten. The SELECT query would quickly become very hard, almost unmanagable, and would have horrible performance. That is where a temporary table comes in. Using this example we'd have a temp table called customers_provider with the following columns: id, customer_name, area_code, phone_number, provider. Then we'd insert all that information into the temporary table where we'd then perform the following SELECT:
SELECT provider FROM customers_provider WHERE customer_name = 'John Doe';
That is much cleaner to read, easier to write, and will be a lot faster in terms of performance. <h3>Creating & Inserting Into Temporary Tables</h3> Creating a temporary table is similar to creating a standard base table. Instead of doing CREATE TABLE tbl_name... you'd instead do CREATE TEMPORARY TABLE tbl_name... The rest of the syntax is exactly the same as when creating a standard base table. When it comes to loading a temporary table with data, or manipulating data in any way, you would structure your queries with the same syntax you'd use against a standard base table. That is because the DBMS will view it as a table, just one that gets deleted (dropped) at the end of the session. <h3>Conclusion</h3> With all that being said it would seem that it is the best route to go using Temporary Tables. In most cases it typically is, but you need to watch out for ghosting performance lost. This means that while you are doing temporary tables in hopes of getting a performance gain, you are actually losing performance by doing so. It all comes down to knowing if you should use a temporary table, a view, or a nested SELECT.
Ronald Steelman <a href="http://mysqlexchange.com">MySQLExchange.com</a>
