ITT #14: Optimizing MySQL Queries

Combine MySQL Queries with Joins to Simplify Queries and Save Time

DISCLAIMER: I am by no means a MySQL optimization expert, and I can't guarantee that the information provided in this blog entry will take a query from painfully slow to blazing fast. I can, however, guarantee that this entry was written with the best intentions and aims to explore some of the more advanced features of MySQL.

This week, I wanted to share some of my recent experiments with MySQL queries in regard to lowering the number of requests sent to the database server and, hopefully, avoiding the bottleneck that can form on sites that rely heavily on database interaction.

What Are MySQL Joins?

A JOIN in MySQL is a tool that allows developers to combine one or more tables in a query. There are a few flavors of JOIN, but we'll be focusing on LEFT JOIN today.

A LEFT JOIN in MySQL combines rows from two tables. We're able to narrow our searches as usual using a WHERE clause, and we can specify how the tables are joined with a USING clause.

A Simple Example

It's tough to explain JOIN in plain English without getting lost in a bunch of technical jargon (as I'm sure anyone who ventures into the MySQL manual is already aware). It's far easier to provide a concrete example to illustrate the way that LEFT JOIN works.

We'll be using three tables in this example: users, lists, and items. These three tables will be the basis of a theoretical wish list application, with a user's unique ID and name stored in the users table, unique list ID and user ID stored in lists, and individual item IDs, containing list ID, and item text in the items table.

Table: users
+---------+-----------------+
| user_id | user_name       |
+---------+-----------------+
| 1       | Jason Lengstorf |
+---------+-----------------+
| 2       | John Doe        |
+---------+-----------------+

Table: lists
+---------+---------+
| list_id | user_id |
+---------+---------+
| 1       | 2       |
+---------+---------+
| 2       | 1       |
+---------+---------+

Table: items
+---------+---------+----------------------+
| item_id | list_id | item_text            |
+---------+---------+----------------------+
| 1       | 1       | iPod Touch           |
+---------+---------+----------------------+
| 2       | 1       | Chuck Season 2       |
+---------+---------+----------------------+
| 3       | 1       | Nike Free Shoes      |
+---------+---------+----------------------+
| 4       | 2       | F-Stop Maverick Bag  |
+---------+---------+----------------------+
| 5       | 2       | Bon Iver Tickets     |
+---------+---------+----------------------+
| 6       | 2       | Rogue Mocha Porter   |
+---------+---------+----------------------+

Imagine we've been supplied with a user's ID and need to retrieve the items on his wish list. Without using JOIN, we would need two MySQL queries: one to retrieve the list ID associated with the user, and one to retrieve all the items associated with that list ID.

Example 1 (Not Using JOIN)

<?php
	// Establish a MySQL connection
	$link = new PDO(DBINFO, DBUSER, DBPASS);

	// Retrieve the list ID associated with the user ID
	$sql = "SELECT list_id
			FROM lists
			WHERE user_id = ?
			LIMIT 1";
	$stmt = $link->prepare($sql); // Prepare the statement
	if($stmt->execute(array($_POST['user_id'])))
	{
		$row = $stmt->fetch(); // Load the results in an array
		$list_id = $row['list_id'];
		$stmt->closeCursor(); // Free memory used in this query

		// Retrieve the items associated with the list ID
		$sql = "SELECT item_text
				FROM items
				WHERE list_id = $list_id";
		foreach($link->query($sql) as $row) {
			// Output the wish list items
			echo $row['item_text'], "<br />";
		}
	}
?>

Assuming the user has supplied the user id of 1, we would see the following output:

F-Stop Maverick Bag
Bon Iver Tickets
Rogue Mocha Porter

This returns the desired result, but sending two queries just feels inefficient. Fortunately, we can shorten this script to just one query using a JOIN.

Example 1 (Using JOIN)

<?php
	// Establish a MySQL connection
	$link = new PDO(DBINFO, DBUSER, DBPASS);

	// Retrieve the items associated with the user ID
	$sql = "SELECT item_text
			FROM lists
			LEFT JOIN items
			USING (list_id)
			WHERE user_id = ?";
	$stmt = $link->prepare($sql); // Prepare the statement
	if($stmt->execute(array($_POST['user_id'])))
	{

		// Loop through the returned results
		while($row = $stmt->fetch()) {
			// Output the wish list items
			echo $row['item_text'], "<br />";
		}

		$stmt->closeCursor(); // Free memory used in this query
	}
?>

Using a LEFT JOIN, we were able to combine the lists and items tables, grouping entries by their list IDs. Essentially, by using a JOIN, we're temporarily creating a new table that looks like this:

SELECT * FROM lists LEFT JOIN items USING (list_id)
+---------+---------+---------+---------------------+
| list_id | user_id | item_id | item_text           |
+---------+---------+---------+---------------------+
| 1       | 2       | 1       | iPod Touch          |
+---------+---------+---------+---------------------+
| 1       | 2       | 2       | Chuck Season 2      |
+---------+---------+---------+---------------------+
| 1       | 2       | 3       | Nike Free Shoes     |
+---------+---------+---------+---------------------+
| 2       | 1       | 4       | F-Stop Maverick Bag |
+---------+---------+---------+---------------------+
| 2       | 1       | 5       | Bon Iver Tickets    |
+---------+---------+---------+---------------------+
| 2       | 1       | 6       | Rogue Mocha Porter  |
+---------+---------+---------+---------------------+

With all the information available from both tables, we're able to use our user's ID to directly select wish list items.

Going a Step Further

Our wish list isn't very useful if it only shows a list of products. In order to make any sense, we should probably show the user's name to indicate who it is that wants the displayed items.

This presents a problem for us, however, because we now need to pull information from all three tables in order to gather all the necessary data.

Example 2 — Combining Three Tables Using LEFT JOIN

<?php
	// Establish a MySQL connection
	$link = new PDO(DBINFO, DBUSER, DBPASS);

	// Retrieve the user's name and associated list items
	$sql = "SELECT user_name, item_text
			FROM users
			LEFT JOIN lists
			USING (user_id)
			LEFT JOIN items
			USING (list_id)
			WHERE user_id = ?";
	$stmt = $link->prepare($sql); // Prepare the statement
	if($stmt->execute(array($_POST['user_id'])))
	{

		// Loop through the returned results
		while($row = $stmt->fetch()) {

			// Save the user name
			$user_name = $row['user_name'];

			// Create an array of items
			$items[] = $row['item_text'];
		}

		$stmt->closeCursor(); // Free memory used in this query

		// Output the user's name and identify what we're displaying
		echo "$user_name's Wish List<br /><br />n";

		// Loop through the items and output to the browser
		foreach($items as $item) {
			echo $item, "<br />";
		}
	}
?>

With the above query, we effectively retrieve the user's name from the users table, then pull the list ID that matches the user's ID, and finally retrieve all items that match the list ID.

The above script will output the following, assuming user ID 1 was passed:

Jason Lengstorf's Wish List

F-Stop Maverick Bag
Bon Iver Tickets
Rogue Mocha Porter

By combining the queries using LEFT JOIN, we create a much easier to read snippet of code. Also, by reducing the number of queries run, we can hope to see faster execution in our scripts.

Summary

Like I said, I'm not a MySQL optimization expert, but from what I've read and my limited testing, it seems pretty safe to say that using JOIN to reduce the number of queries in our scripts is a good way to not only enhance the readability of our scripts, but also to grease them up for faster execution.

Do you know any tricks for speeding up MySQL queries? How do you feel about using JOIN in queries? Let me know in the comments!

Posted May 05, 2009 by Jason Lengstorf.
This entry is filed under instant tip tuesday, mysql, and performance.