mysql:order by and limit给出错误的结果

MySQL 5.1.26版

我选了一个包含where、order by和limit子句的选项,结果是错误的。 只有当order by使用id列时才有问题。

我看过mysql手册 LIMIT Optimization

我从阅读手册中推测,主键ID上的索引有问题。但是我不知道从这里应该去哪里…

问题:我应该怎么做才能最好地解决这个问题?

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 |
+----+-------------+------------------+-------+--------------------------------------+--------------------------------------+---------+------+------+-------------+

添加显示创建表开票\发票结果:

Table -- billing_invoices
Create Table --
CREATE TABLE `billing_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `invoice_date` date NOT NULL,
  `prior_invoice_id` int(11) DEFAULT NULL,
  `closing_balance` decimal(8,2) NOT NULL,
  `note` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `monthly_invoice` tinyint(1) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_billing_invoices_on_account_id` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1337 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

添加更多:

我现在看到在我的开发机器上,一切都正常工作。那台机器的版本是5.1.26-rc-log

在我身上 生产 machine,在问题所在,我看到version()返回5.1.26-rc-percona-log

所以现在,我想问题出在Percona软件上?

添加更多:

在这一点上,我认为它是Percona InnoDB驱动程序中的一个bug。我放了一个 question to their forum . 作为一项即时的工作,我将由Created_at订购。我还将研究升级我的系统上的数据库,看看是否有帮助。

感谢Rabbott和MDMA的帮助。我也感谢你的帮助,我没有做傻事,这真的是个问题。