title: mariadb group by slow on small subset
body: <p>I want to group a subset of a fairly large table using this query on mariadb:</p>
  
  <h3>Q1</h3>
  
  <pre><code>SELECT count(item_group_id) 
  FROM item 
  WHERE created &gt; [-1 DAY] 
  GROUP BY item_group_id
  </code></pre>
  
  <p>The subset of <code>created &gt; [-1 DAY]</code> is only about 200 rows. The entire set of table <code>item</code> has <strong>over half a million</strong> rows.  </p>
  
  <p>The query takes over <strong>9 Seconds!</strong></p>
  
  <p>Without the <code>GROUPY BY</code> the query takes 40ms!</p>
  
  <p>This is confusing to me as I thought the <code>GROUP BY</code> statement should be applied <strong>AFTER</strong> the <code>WHERE</code> statement.</p>
  
  <p>So I try a different query, to force mariadb to apply the <code>GROUP BY</code> only on the subset of 200 rows:</p>
  
  <h3>Q2</h3>
  
  <pre><code>SELECT count(item_group_id) 
  FROM (SELECT * FROM item WHERE created &gt; [-1 DAY]) t 
  GROUP BY item_group_id
  </code></pre>
  
  <p>Still, Q2 Gives me the exact same behaviour as Q1....</p>
  
  <p><strong><em>Funny enough, Q3 worked and reduced execution time from 9s to 100ms:</em></strong></p>
  
  <h3>Q3</h3>
  
  <pre><code>SELECT count(item_group_id) 
  FROM (SELECT * FROM item WHERE created &gt; [-1 DAY] LIMIT 100000000) t 
  GROUP BY item_group_id
  </code></pre>
  
  <hr>
  
  <h3>Explain for Q2</h3>
  
  <pre><code>+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
  | id | select_type | table | type  | possible_keys |   key   | key_len | ref  |  rows  |    Extra    |
  +----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
  |  1 | SIMPLE      | item  | index | NULL          | PRIMARY |     108 | NULL | 643167 | Using index |
  +----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
  </code></pre>
  
  <h3>Explain for Q3</h3>
  
  <pre><code>+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
  | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
  +----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
  | 1  | PRIMARY     | &lt;derived2&gt; | ALL  | NULL          | NULL | NULL    | NULL | 643207 | Using temporary; Using filesort |
  +----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
  | 2  | DERIVED     | item       | ALL  | NULL          | NULL | NULL    | NULL | 643207 |                                 |
  +----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
  </code></pre>
  
  <p>Using the <code>LIMIT [large number]</code> did exactly what I wanted and created the temp table <strong>before</strong> applying the <code>GROUP BY</code> </p>
  
  <p>What exactly is the <code>LIMIT</code> doing to make mariadb behave differently? Can anyone explain this please?</p>
  
  <p>cheers!</p>
  
  <p>Edit: <code>SHOW CREATE TABLE</code>.</p>
  
  <pre><code>CREATE TABLE `item` (
    `id` char(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:uuid)',
    `item_group_id` char(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:uuid)',
    `content_id` char(36) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '(DC2Type:uuid)',
    `section_id` char(36) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '(DC2Type:uuid)',
    `person_id` char(36) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '(DC2Type:uuid)',
    `created` datetime NOT NULL,
    `updated` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `IDX_D4707EBD57B8F0DE` (`item_group_id`),
    KEY `IDX_D4707EBDD07ECCB6` (`content_id`),
    KEY `IDX_D4707EBDF639F774` (`section_id`),
    KEY `IDX_D4707EBD9395C3F3` (`person_id`),
    CONSTRAINT `FK_D4707EBD57B8F0DE` FOREIGN KEY (`item_group_id`) REFERENCES `item_group` (`id`),
    CONSTRAINT `FK_D4707EBD9395C3F3` FOREIGN KEY (`person_id`) REFERENCES `pseron` (`id`) ON DELETE SET NULL,
    CONSTRAINT `FK_D4707EBDD07ECCB6` FOREIGN KEY (`content_id`) REFERENCES `content` (`id`) ON DELETE SET NULL,
    CONSTRAINT `FK_D4707EBDF639F774` FOREIGN KEY (`section_id`) REFERENCES `section` (`id`) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  </code></pre>