Chapter 5: Joins, Temporary Tables, and Transactions

The MySQL Developer Zone received permission to publish excerpts from the book "Beginning MySQL Database Design and Optimization: From Novice to Professional" by Jon Stephens and Chad Russell, published in 2004 by Apress (ISBN 1-59059-332-4). Our first selection is chapter 5 about Joins, Temporary Tables, and Transactions.

In this chapter, we'll discuss three additional features you can use to speed up your MySQL applications. While these aren't directly related to one another, each represents an opportunity to decrease the amount of database or code overhead required to perform useful tasks with MySQL by combining queries or operations on the code level into fewer units that perform more work.

• Joins allow for the selection of data from multiple tables using a single SQL statement.
• Temporary tables provide a way to organize data derived from queries into new tables, which can themselves be queried repeatedly over the lifetime of a MySQL user session.
• Transactions allow you to group together related operations into logical units in such a way that all operations either succeed or fail together.

We will spend some time with each of these features, discussing what it is, how it works, and how you can put it to use in your applications.

The rationale behind joins is relatively simple: it's more efficient to issue a single query than to use a series of them, with the resultset from the first query providing the conditions for one or more additional queries. There are several types of joins, which are distinguished chiefly by how they treat values in one table column that aren't matched in the related column of the other table; we'll cover each of these in turn. In addition, we'll discuss the two basic styles for join notation (theta-style and ANSI-style) and the variations on these that are available in MySQL.

The use of temporary tables is another way to save time and effort, particularly when dealing with several queries that return very large and similar resultsets. When you find yourself dealing with the same subset of table data several times in a single session, it's often faster and more economical to obtain it once and store it in a temporary table, rather than either saving the data in a programming structure (such as an array or hash) or repeating a complex join several times. If you're using several resultsets that contain a large proportion of data in common, it can also make sense to obtain a single resultset that has all the data that's required, store this in a temporary table, and then select from this temporary table as needed. Temporary tables are very convenient to use in MySQL because they are unique to the user session in which they were created. We'll explain just what this means, as well as how to use temporary tables.

Transactions are beneficial because they make it much easier to guarantee data integrity. It's also much more efficient to use transactions than to attempt to perform each query separately in your application logic, testing for its success or failure, and then undoing any previously successful operations in the event that one does fail. By using InnoDB or Berkeley DB (BDB) tables and transactions, you can let MySQL handle this task for you. Using transactions is not necessarily faster in and of itself than not using them; in fact, MyISAM tables (which don't support transactions) are faster than either InnoDB or BDB tables (which do). However, you'll almost certainly save time in development, and your applications will require less code, because you don't need to test and possibly undo each query individually. In this chapter, we'll cover the basic theory of transactions and how they're implemented in MySQL. Later in this book (in Chapter 7), you'll see how these are used in PHP, Python, and Perl.

Read On

The whole article is long, so we thought it better to make it a PDF. To download, click here (no registration required!) You could even print out the PDF so you can peruse at your leisure.