Bug #55090 ndb query faster with ORDER BY than without

• Submitted: 8 Jul 2010 16:55
• Reporter: Andrew Hutchings
• Status: Verified
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[8 Jul 2010 16:55] Andrew Hutchings

Description:
When running queries against a cluster with several data nodes (12 in my tests).  The following query is faster with the ORDER BY than without.  I would expect at worst the reverse to be true and at best the performance to be the same:

select panelid FROM panel WHERE active=1 AND statusid=3 order by panelid;
...
80813 rows in set (1.32 sec)

select panelid FROM panel WHERE active=1 AND statusid=3;
...
80813 rows in set (3.68 sec)

mysql> explain select panelid FROM panel WHERE active=1 AND statusid=3 order by panelid\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: panel
         type: ref
possible_keys: STATUSID,ACTIVE
          key: STATUSID
      key_len: 5
          ref: const
         rows: 10
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

mysql> explain select panelid FROM panel WHERE active=1 AND statusid=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: panel
         type: ref
possible_keys: STATUSID,ACTIVE
          key: STATUSID
      key_len: 5
          ref: const
         rows: 10
        Extra: Using where
1 row in set (0.00 sec)

panelid is the primary key.

How to repeat:
Test case to be attached.

[17 May 2012 21:02] Ole John Aske

I believe this to be caused by Bug#57601: 'OPTIMIZER IS OVERLY EAGER TO REQUEST ORDERED ACCESS'. Thus the unsorted query was requested as an sorted results set from the handler even if not required when first/next handler access methods was used.