光の彼方には何があるのだろう？ニフラム…(自分に手をかざしながら)

生活とLife

2ヶ月ちょい前にMySQLの不思議なbugに遭遇したので報告させて頂きます。

  • MySQLのバージョン: 5.1.42-community-log
  • OS: CentOS5 Final
  • 現象: order by PRIMARY_KEY desc limit N で該当するレコードが取得できない

どのような事が起こるかは、http://stackoverflow.com/questions/2844699/mysql-order-by-and-limit-gives-wrong-resultからコピペ。

Works correctly:
mysql> SELECT id, created_at FROM billing_invoices 
       WHERE (billing_invoices.account_id = 5) ORDER BY id DESC ;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 1336 | 2010-05-14 08:05:25 |
| 1334 | 2010-05-06 08:05:25 |
| 1331 | 2010-05-05 23:18:11 |
+------+---------------------+
3 rows in set (0.00 sec)

WRONG result when limit added! Should be the first row, id - 1336
mysql> SELECT id, created_at FROM billing_invoices 
       WHERE (billing_invoices.account_id = 5) ORDER BY id DESC limit 1;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 1331 | 2010-05-05 23:18:11 |
+------+---------------------+
1 row in set (0.00 sec)

Works correctly:
mysql> SELECT id, created_at FROM billing_invoices 
       WHERE (billing_invoices.account_id = 5) ORDER BY created_at DESC ; 
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 1336 | 2010-05-14 08:05:25 |
| 1334 | 2010-05-06 08:05:25 |
| 1331 | 2010-05-05 23:18:11 |
+------+---------------------+
3 rows in set (0.01 sec)

Works correctly with limit:
mysql> SELECT id, created_at FROM billing_invoices 
       WHERE (billing_invoices.account_id = 5) ORDER BY created_at DESC limit 1;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
| 1336 | 2010-05-14 08:05:25 |
+------+---------------------+
1 row in set (0.01 sec)

Additional info:
explain SELECT id, created_at FROM billing_invoices WHERE (billing_invoices.account_id = 5) ORDER BY id DESC limit 1;
+----+-------------+------------------+-------+--------------------------------------+--------------------------------------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys                        | key                                  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+--------------------------------------+--------------------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | billing_invoices | range | index_billing_invoices_on_account_id | index_billing_invoices_on_account_id | 4       | NULL |    3 | Using where |
+----+-------------+------------------+-------+--------------------------------------+--------------------------------------+---------+------+------+-------------+

ORDER BY だけなら問題ないが LIMIT を指定すると type が index から range にかわりなんかうんこ。
対策は force index するか created_at に index はっておいて created_at でソートすればよいと思います。
http://bugs.mysql.com/bug.php?id=37830 をみると2008年頃に fix したのに今年また大復活を遂げたみたいなのでMySQL5.1系つかってる人は注意したほうがよいでしょう。

話がかわりますが、僕は


をわり。