GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

order by and limit gives wrong result

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • order by and limit gives wrong result

    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?

  • #2
    I doubt very much that the problem is with the Percona software. It is almost impossible to imagine that the changes we made to InnoDB and MySQL could cause this. I have a couple of suggestions.

    First, the tables are probably in need of repair or upgrade. Did you upgrade MySQL from 5.0 without upgrading the tables? InnoDB does not support REPAIR TABLE, but you can simply use OPTIMIZE or another command that will alter the table. This will rebuild it.

    My second suggestion is to consider upgrading from 5.1.26 to the latest 5.1 series. 5.1 had a ton of bug fixes with each new release for a long time after 5.1.26. I would consider 5.1.26 to be very buggy. A lot of those bugs were in the optimizer, and affected things like the problems you are describing.

    Comment


    • #3
      5.1.30 was actually the GA release:
      http://dev.mysql.com/doc/refman/5.1/en/news-5-1-30.html

      You are using a release candidate / not the final product.

      Comment

      Working...
      X