MySQL ver 5.1.26 with PerconaInnoDB
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 | ±—±------------±-----------------±------±-------------------------------------±-------------------------------------±--------±-----±-----±------------+
Added SHOW CREATE TABLE billing_invoices result:
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
I now see that on my development machine, everything is working correctly. That machine has version VERSION() of 5.1.26-rc-log
On my production machine, where the problem is, I see that VERSION() returns 5.1.26-rc-percona-log
So at this point, I’m thinking the problem is with the percona software?