« MySQL Pop Quiz #10 | Main | MySQL Pop Quiz #12 »
MySQL Pop Quiz #11
By Carsten | March 4, 2008
Today’s quiz comes out of an e-mail conversation with fenixshadow, aka 
On my system (5.0.45-Debian_1ubuntu3.1-log) , I run these commands:
CREATE TABLE t ( ID INT NOT NULL PRIMARY KEY, PID INT, FOREIGN KEY (PID) REFERENCES t(ID) ) ENGINE=INNODB; INSERT INTO t VALUES (1, NULL); INSERT INTO t VALUES (2, NULL);
SELECT * FROM t yields the expected result:
+----+------+ | ID | PID | +----+------+ | 1 | NULL | | 2 | NULL | +----+------+
I now do
UPDATE t SET PID = 2 WHERE ID = 1
and observe the result of SELECT * FROM t again:
+----+------+ | ID | PID | +----+------+ | 2 | NULL | | 1 | 2 | +----+------+
Question 1: Can you easily explain why the rows are now in reverse order?
Question 2: Is there anything you can or should do about this?
Do you have your own favorite MySQL question? Is there something in MySQL that has you scratching your head? Send your ideas for new pop quiz questions to carsten (at) bitbybit (dot) dk. Send it in, even if you don’t know the answer — that only makes it more interesting.
More Pop Quiz entries: WWW or RSS.
March 4th, 2008 at 8:44 am
This is a relational database, and rows do not have an implicit order. Order is achieved by an ORDER BY clause, and if you do not specify an order by clause, the rows are returned in an implementation-dependent order.
But speaking of that implementation-dependent order, when you perform the UPDATE, InnoDB creates a new record version for the row with ID=1, and stored it in the first available free space, which is after the row with ID=2. When you query without specifying an ORDER BY, the order seems to be determined by the physical storage of the records.
The new record couldn’t be stored in the physical place of the old record, because that version must exist until it’s certain that there are no active transactions reading it. When that is determined, the old version can be garbage-collected and its space can finally be be recycled.
March 4th, 2008 at 11:58 am
The funny thing is
mysql> SELECT * FROM t;
+—-+——+
| ID | PID |
+—-+——+
| 2 | NULL |
| 1 | 2 |
+—-+——+
2 rows in set (0.00 sec)
mysql> SELECT ID FROM t;
+—-+
| ID |
+—-+
| 1 |
| 2 |
+—-+
2 rows in set (0.00 sec)
Actually in InnoDB, the primary key stays ordered, so if you only select the ID, MySQL will read the data from the index directly, showing it in the previous order.
OMG, I’m becoming a MySQL geek! Should I quit training??
March 4th, 2008 at 5:40 pm
Check the EXPLAIN output for each SELECT. You might find that this:
SELECT * FROM t;
does an index scan (not a full table scan) on the secondary index on PID (since the PK value is also available in that index in InnoDB), making the secondary index on PID a covering index for the above query and producing a default ordering by PID, then ID, while this:
SELECT ID FROM t;
Likely does an index scan on primary key, which for InnoDB will essentially be a full table scan in default order of ID, then PID…
-jay
March 4th, 2008 at 6:38 pm
@koke: No, all you have to do is to start thinking.
March 4th, 2008 at 7:27 pm
If you look at the output of explain for the SELECT * FROM t query before and after the update, you’ll see that MySQL is using the [b-tree] PID index to read the data. In InnoDB, an index on (PID) is actually internally a covering index on (PID, ID). Note that this would not apply to MyISAM tables as their indexes are not implicit covering indexes.
Updating the table updated the PID index, which actually re-ordered the nodes in the index. As you did not return an ORDER BY clause, the data gets returned in the order it is read in the PID index.
March 4th, 2008 at 8:02 pm
Finally I find an example that shows you need to use ORDER BY
March 6th, 2008 at 9:11 am
[...] mentioned in Pop Quiz #11, we can’t actually be sure of the order in which rows are returned. But in this particular [...]
March 31st, 2008 at 9:09 am
[...] ???????????? ? ?????????? Carsten Pedersen. ???????? [...]