Bug #55090 ndb query faster with ORDER BY than without
Submitted: 8 Jul 2010 16:55
Reporter: Andrew Hutchings Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: 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.