The Planet Blog

Often, developers and administrators come across situations in which the only apparent solutions are overly complex and require significant effort to implement. Most of the time this happens because they don’t know something else – and usually something easier – exists.

This practice was evident in a recent case where a customer didn’t know it was possible to do table joins inside an UPDATE statement.

Here’s an example of a task he was faced with: You have a user table and a user_subscription table; the former contains information about each user, while the latter contains start and end dates for particular subscription products for each user. You want to extend the access period for each user that lives in the UK using product #5.

A common way of tackling this is to create a temporary table and populate it with all of the userIds from the user table that has the country = 'UK' property, then update the subscription table specifying only those userIds in the temporary table:

CREATE TEMPORARY TABLE ukUsers (userId INT(11));
INSERT INTO ukUsers SELECT userId FROM user WHERE country = 'UK';
UPDATE user_subscription 
  SET expireDate = DATE_ADD(expireDate, INTERVAL 3 DAY)
  WHERE userId IN (SELECT * FROM ukUsers) AND productId = 5;

While the code outlined above gets the job done, it could be better written. Let’s see how the SELECT statement could be used to retrieve the user information (i.e. get all UK users with subscription to product #5):

SELECT u.firstName, u.lastName
FROM user u LEFT JOIN user_subscription s USING(userId)
WHERE u.region = 'UK' AND s.productId = 5;

Now, let’s translate the above get into an UPDATE statement:

UPDATE user u LEFT JOIN user_subscription s USING(userId)
SET s.expireDate = DATE_ADD(s.expireDate, INTERVAL 3 DAY)
WHERE u.region = 'UK' AND s.productId = 5;

It’s as simple as that! We no longer need the temporary table, and it saves us a couple of extra SQL commands.

For more information on MySQL UPDATE syntax, visit: http://dev.mysql.com/doc/refman/5.1/en/update.html

-Matthew

Let’s face it — we’ve all seen some bad code in our time. During my eight-plus years of using PHP, I’ve inherited several legacy systems, and when reading over the previous programmer’s code, I often sit back and say, “What the heck was this guy thinking?”

Combine a programmer’s bad code with his or her lack of understanding about how databases work, and you’ve got one inefficient, unresponsive Web site.

The best example of this that I’ve encountered was one programmer’s attempt simply to get the number of rows in a table with a particular column value. This was his solution:

$rowCount = 0;
$res = mysql_query("SELECT * FROM users_activity");
while($row = mysql_fetch_row($res))
{
  if($row[‘activity] == ‘LOGIN')
  {
    $rowCount++;
  }
}

I hope all of you are cringing as badly as I did when I came across it the first time. For those of you that don’t understand why this is horribly inefficient, I’ll EXPLAIN.

MySQL has an often overlooked but extremely powerful built-in query analyzer that allows you to take any SELECT SQL statement, preface it with the keyword EXPLAIN, and get an analysis of the query with lots of information on how to optimize it.

Let’s use the above SQL statement on a table with over 4 million rows. Here’s the schema:

CREATE TABLE `users_activity` (
  `userId` int(11) NOT NULL,
  `activity` varchar(30) NOT NULL DEFAULT 'LOGIN',
  `notes` varchar(200) DEFAULT NULL,
  `timestamp` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  KEY `userId_2` (`userId`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The first concern we have is that the query has no WHERE clause. Not having this clause requires MySQL to scan the entire table (full_table_scan), which usually involves reading the entire table from disk into memory, and if you only need to do this once, you just wasted all that memory. With EXPLAIN, MySQL can show us exactly how many rows it checks:

>EXPLAIN SELECT * FROM users_activity
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users_activity
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4060232
        Extra:

MySQL will have to examine 4,060,232 rows to satisfy this query. Let’s add a WHERE clause and see how that helps:

>EXPLAIN SELECT * FROM users_activity WHERE activity = 'LOGIN'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users_activity
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4060232
        Extra: Using where

Hmm … Well, that didn’t change the number of rows evaluated, did it? The query optimizer still had to look at all the rows, one by one, to determine if the activity column was equal to ‘LOGIN’. Even with our WHERE clause, this query is still almost as bad as the original author’s PHP code. Why did MySQL have to do this? To answer that, take a look at the NULL value returned for possible_keys. This result shows that MySQL was unable to find an index for the column to help narrow down the search. Let’s add an index and see what happens:

>ALTER TABLE `users_activity` ADD INDEX (`activity`);

>EXPLAIN SELECT * FROM users_activity WHERE activity = 'LOGIN'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users_activity
         type: ref
possible_keys: activity
          key: activity
      key_len: 92
          ref: const
         rows: 422436
        Extra: Using where

Very nice. We immediately reduced the number of rows that MySQL has to examine from 4,060,232 to 422,436. An 89 percent reduction in virtually no time at all.

This is just one of many scenarios in which using EXPLAIN can help you write better SQL statements. You can turn on the log_queries_not_using_indexes option to find out which SQL statements are not using indexes. By using this log and EXPLAIN, you can enhance the performance of your MySQL server by leaps and bounds.

For further information on EXPLAIN, visit http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

-Matthew