Databases Reference
In-Depth Information
mysql>
SET @rownum := 0;
mysql>
SELECT actor_id, first_name, @rownum AS rownum
->
FROM sakila.actor
->
WHERE @rownum <= 1
->
ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);
The answer to most unexpected user-defined variable behavior can be found by
running
EXPLAIN
and looking for “Using where,” “Using temporary,” or “Using filesort”
in the
Extra
column.
The last example introduced another useful hack: we placed the assignment in the
LEAST()
function, so its value is effectively masked and won't skew the results of the
ORDER BY
(as we've written it, the
LEAST()
function will always return
0
). This trick is
very helpful when you want to do variable assignments solely for their side effects: it
lets you hide the return value and avoid extra columns, such as the
dummy
column we
showed in a previous example. The
GREATEST()
,
LENGTH()
,
ISNULL()
,
NULLIF()
,
IF()
, and
COALESCE()
functions are also useful for this purpose, alone and in combination, because
they have special behaviors. For instance,
COALESCE()
stops evaluating its arguments as
soon as one has a defined value.
Writing a lazy UNION
Suppose you want to write a
UNION
query that executes the first branch of the
UNION
and, if it finds any rows, skips the second branch. You might do this when you're
looking for a row in a table that has “hot” rows that are accessed frequently, and another
table with identical rows that happen to be accessed less often. (Partitioning hot and
cold data can be a helpful way to increase cache efficiency.)
Here's a query that will look for a user in two places—the main user table, and a table
of users who haven't visited in a long time and so have been archived for efficiency:
20
SELECT id FROM users WHERE id = 123
UNION ALL
SELECT id FROM users_archived WHERE id = 123;
That query works, but it'll look for the row in the
users_archived
table even if it is
found in the
users
table. We can prevent that with a lazy
UNION
, which lazily accesses
the second table only if there are no results in the first one. We'll assign to a user variable
called
@found
when a row is found. To make that happen, we need to place the assign-
ment in the column list, so we'll use the
GREATEST
function as a container for the as-
signment so we don't get an extra column in the results. To make it easier to see which
table the results came from, we'll add a column containing the table name. Finally, we
need to reset the user variable to
NULL
at the end of the query, so it has no side effects
and can be executed repeatedly. Here's the query:
20. Baron thinks that some social networks archive his data between his very infrequent visits. When he logs
in, his account doesn't seem to exist; but then he gets an email a few minutes later welcoming him back,
and
voilà
, his account has been recovered. This is a smart optimization for antisocial users, which we'll
discuss further in
Chapter 11
.
