Databases Reference
In-Depth Information
->
LIMIT 10
->
) as der;
+----------+-----+------+-------+
| actor_id | cnt | rank | dummy |
+----------+-----+------+-------+
| 107 | 42 | 1 | 42 |
| 102 | 41 | 2 | 41 |
| 198 | 40 | 3 | 40 |
| 181 | 39 | 4 | 39 |
| 23 | 37 | 5 | 37 |
| 81 | 36 | 6 | 36 |
| 106 | 35 | 7 | 35 |
| 60 | 35 | 7 | 35 |
| 13 | 35 | 7 | 35 |
| 158 | 35 | 7 | 35 |
+----------+-----+------+-------+
Avoiding retrieving the row just modified
What if you want to update a row, but then you want to retrieve some information
about it without actually accessing the row again? Unfortunately, MySQL doesn't sup-
port anything like PostgreSQL's
UPDATE RETURNING
functionality, which would be
useful for this purpose. But you can use variables instead. For example, one of our
customers wanted a more efficient way to update a row's timestamp to the current time,
and then find out what that time was. The code looked like the following:
UPDATE t1 SET lastUpdated = NOW() WHERE id = 1;
SELECT lastUpdated FROM t1 WHERE id = 1;
We rewrote those queries to use a variable instead, as follows:
UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;
There are still two queries and two network round-trips, but the second query doesn't
access any tables, so it's faster. (Your mileage may vary. This might not be worthwhile
for you, but it was for this customer.)
Counting UPDATEs and INSERTs
What if you're using
INSERT ON DUPLICATE KEY UPDATE
and you want to know how many
rows were inserted without conflicting with existing rows, versus the rows that caused
a conflict and updated a row? Kristian Köhntopp posted a solution to this problem on
his blog.
19
The essence of the technique follows:
INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
ON DUPLICATE KEY UPDATE
c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );
