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

MySQL 5.1.26版

我选了一个包含where、order by和limit子句的选项,结果是错误的。

只有当order by使用id列时才有问题。

我从阅读手册中推测,主键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的帮助。我也感谢你的帮助,我没有做傻事,这真的是个问题。