title: Huge performance difference when using GROUP BY vs DISTINCT
body: <p>I am performing some tests on a <code>HSQLDB</code> server with a table containing 500 000 entries. The table has no indices. There are 5000 distinct business keys. I need a list of them.</p>
  <p>Naturally I started with a <code>DISTINCT</code> query:</p>
  <pre><code>SELECT DISTINCT business_key
  FROM memory
  WHERE concept &lt;&gt; 'case'   OR 
        attrib  &lt;&gt; 'status' OR 
        value   &lt;&gt; 'closed';
  </code></pre>
  <p>It takes around 90 seconds!!!</p>
  <p>Then I tried using <code>GROUP BY</code>:</p>
  <pre><code>SELECT business_key
  FROM memory
  WHERE concept &lt;&gt; 'case'   OR 
        attrib  &lt;&gt; 'status' OR
        value   &lt;&gt; 'closed';
  GROUP BY business_key
  </code></pre>
  <p>And it takes 1 second!!!</p>
  <p>Trying to figure out the difference I ran <code>EXLAIN PLAN FOR</code> but it seems to give the same information for both queries.</p>
  <p><code>EXLAIN PLAN FOR DISTINCT ...</code></p>
  <pre><code>isAggregated=[false]
  columns=[
    COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
  ]
  [range variable 1
    join type=INNER
    table=MEMORY
    alias=M
    access=FULL SCAN
    condition = [    index=SYS_IDX_SYS_PK_10057_10058
      other condition=[
      OR arg_left=[
       OR arg_left=[
        NOT_EQUAL arg_left=[
         COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
         VALUE = case, TYPE = CHARACTER]] arg_right=[
        NOT_EQUAL arg_left=[
         COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
         VALUE = status, TYPE = CHARACTER]]] arg_right=[
       NOT_EQUAL arg_left=[
        COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
        VALUE = closed, TYPE = CHARACTER]]]
    ]
  ]]
  PARAMETERS=[]
  SUBQUERIES[]
  Object References
  PUBLIC.MEMORY
  PUBLIC.MEMORY.CONCEPT
  PUBLIC.MEMORY.ATTRIB
  PUBLIC.MEMORY.VALUE
  PUBLIC.MEMORY.BUSINESS_KEY
  Read Locks
  PUBLIC.MEMORY
  WriteLocks
  </code></pre>
  <p><code>EXLAIN PLAN FOR SELECT ... GROUP BY ...</code></p>
  <pre><code>isDistinctSelect=[false]
  isGrouped=[true]
  isAggregated=[false]
  columns=[
    COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
  ]
  [range variable 1
    join type=INNER
    table=MEMORY
    alias=M
    access=FULL SCAN
    condition = [    index=SYS_IDX_SYS_PK_10057_10058
      other condition=[
      OR arg_left=[
       OR arg_left=[
        NOT_EQUAL arg_left=[
         COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
         VALUE = case, TYPE = CHARACTER]] arg_right=[
        NOT_EQUAL arg_left=[
         COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
         VALUE = status, TYPE = CHARACTER]]] arg_right=[
       NOT_EQUAL arg_left=[
        COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
        VALUE = closed, TYPE = CHARACTER]]]
    ]
  ]]
  groupColumns=[
  COLUMN: PUBLIC.MEMORY.BUSINESS_KEY]
  PARAMETERS=[]
  SUBQUERIES[]
  Object References
  PUBLIC.MEMORY
  PUBLIC.MEMORY.CONCEPT
  PUBLIC.MEMORY.ATTRIB
  PUBLIC.MEMORY.VALUE
  PUBLIC.MEMORY.BUSINESS_KEY
  Read Locks
  PUBLIC.MEMORY
  WriteLocks
  </code></pre>
  <hr />
  <h2>EDIT</h2>
  <p>I did additional tests. With 500 000 records in <code>HSQLDB</code> with all distinct business keys, the performance of <code>DISTINCT</code> is now better - 3 seconds, vs <code>GROUP BY</code> which took around 9 seconds.</p>
  <p>In <code>MySQL</code> both queries preform the same:</p>
  <p>MySQL: 500 000 rows - 5 000 distinct business keys:
  Both queries: 0.5 second
  MySQL: 500 000 rows - all distinct business keys:
  <code>SELECT DISTINCT ...</code> - 11 seconds
  <code>SELECT ... GROUP BY business_key</code> - 13 seconds</p>
  <p>So the problem is only related to <code>HSQLDB</code>.</p>
  <p>I will be very grateful if someone can explain why there is such a drastic difference.</p>