MySQL: order by and limit gives wrong result
- by Larry K
MySQL ver 5.1.26
I'm getting the wrong result with a select that has where, order by and limit clauses.
It's only a problem when the order by uses the id column.
I saw the MySQL manual for LIMIT Optimization
My guess from reading the manual is that there is some problem with the index on the primary key, id. But I don't know where I should go from here...
Question: what should I do to best solve the problem?
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 |
+----+-------------+------------------+-------+--------------------------------------+--------------------------------------+---------+------+------+-------------+
