title: MySQL group-by query perfomance improved by adding aggregation
body: <p>I have the following table in MySQL:</p>
  
  <pre><code>CREATE TABLE `events` (
    `pv_name` varchar(60) COLLATE utf8mb4_bin NOT NULL,
    `time_stamp` bigint(20) unsigned NOT NULL,
    `event_type` varchar(40) COLLATE utf8mb4_bin NOT NULL,
    `has_data` tinyint(1) NOT NULL,
    `data` json DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;
  
  ALTER TABLE `events`
   ADD PRIMARY KEY (`pv_name`,`time_stamp`),
   ADD UNIQUE KEY `has_data` (`pv_name`,`has_data`,`time_stamp`);
  </code></pre>
  
  <p>I am trying to find the distinct set of pv_names that have some row with no data between two given times. Both of the following queries appear to return this information:</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT pv_name FROM events
           WHERE has_data = 0
             AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999
           GROUP BY events.pv_name;
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
  | id | select_type | table  | partitions | type  | possible_keys    | key      | key_len | ref  | rows    | filtered | Extra                    |
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
  |  1 | SIMPLE      | events | NULL       | index | PRIMARY,has_data | has_data | 251     | NULL | 1855281 |     1.11 | Using where; Using index |
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
  
  mysql&gt; EXPLAIN SELECT pv_name, MAX(events.time_stamp) FROM events
           WHERE has_data = 0
             AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999
           GROUP BY events.pv_name;
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
  | id | select_type | table  | partitions | type  | possible_keys    | key      | key_len | ref  | rows   | filtered | Extra                                 |
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
  |  1 | SIMPLE      | events | NULL       | range | PRIMARY,has_data | has_data | 251     | NULL | 203123 |   100.00 | Using where; Using index for group-by |
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
  </code></pre>
  
  <p>What I do not understand is why the second query, which has an extra restriction on what it returns (that I do not need), appears to run in less time than the first. Is there a way to improve the first query to match the efficiency of the second without the aggregation on the <code>time_stamp</code> column?</p>
  
  <p>EDIT:</p>
  
  <p>Per Rick James's suggestion I changed the <code>has_data</code> index:</p>
  
  <pre><code>ALTER TABLE `events`
   ADD PRIMARY KEY (`pv_name`,`time_stamp`), ADD KEY `has_data` (`has_data`,`pv_name`,`time_stamp`);
  </code></pre>
  
  <p>This changed the query reports to:</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT pv_name FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  | id | select_type | table  | partitions | type | possible_keys    | key      | key_len | ref   | rows   | filtered | Extra                    |
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  |  1 | SIMPLE      | events | NULL       | ref  | PRIMARY,has_data | has_data | 1       | const | 267096 |    11.11 | Using where; Using index |
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  1 row in set, 1 warning (0.00 sec)
  
  mysql&gt; EXPLAIN SELECT pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  | id | select_type | table  | partitions | type | possible_keys    | key      | key_len | ref   | rows   | filtered | Extra                    |
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  |  1 | SIMPLE      | events | NULL       | ref  | PRIMARY,has_data | has_data | 1       | const | 267096 |    11.11 | Using where; Using index |
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  1 row in set, 1 warning (0.01 sec)
  </code></pre>
  
  <p>This seems to run faster.</p>
  
  <p>EDIT:</p>
  
  <p>Results for test requested by Rick James:</p>
  
  <pre><code>mysql&gt; FLUSH STATUS;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; SELECT pv_name FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  .
  .
  .
  114480 rows in set (0.34 sec)
  
  mysql&gt; SHOW SESSION STATUS LIKE 'Handler%';
  +----------------------------+--------+
  | Variable_name              | Value  |
  +----------------------------+--------+
  | Handler_commit             | 1      |
  | Handler_delete             | 0      |
  | Handler_discover           | 0      |
  | Handler_external_lock      | 2      |
  | Handler_mrr_init           | 0      |
  | Handler_prepare            | 0      |
  | Handler_read_first         | 0      |
  | Handler_read_key           | 1      |
  | Handler_read_last          | 0      |
  | Handler_read_next          | 125527 |
  | Handler_read_prev          | 0      |
  | Handler_read_rnd           | 0      |
  | Handler_read_rnd_next      | 0      |
  | Handler_rollback           | 0      |
  | Handler_savepoint          | 0      |
  | Handler_savepoint_rollback | 0      |
  | Handler_update             | 0      |
  | Handler_write              | 0      |
  +----------------------------+--------+
  18 rows in set (0.01 sec)
  
  mysql&gt; SELECT COUNT(*) FROM events;
  +----------+
  | COUNT(*) |
  +----------+
  |  3683887 |
  +----------+
  1 row in set (11.66 sec)
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>Timings of runs:</p>
  
  <pre><code>mysql&gt; SHOW INDEXES FROM events;
  +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | events |          0 | PRIMARY  |            1 | pv_name     | A         |      216061 |     NULL | NULL   |      | BTREE      |         |               |
  | events |          0 | PRIMARY  |            2 | time_stamp  | A         |     4450791 |     NULL | NULL   |      | BTREE      |         |               |
  | events |          1 | has_data |            1 | has_data    | A         |         258 |     NULL | NULL   |      | BTREE      |         |               |
  | events |          1 | has_data |            2 | pv_name     | A         |      496542 |     NULL | NULL   |      | BTREE      |         |               |
  | events |          1 | has_data |            3 | time_stamp  | A         |     4390035 |     NULL | NULL   |      | BTREE      |         |               |
  +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  5 rows in set (0.00 sec)
  
  mysql&gt; EXPLAIN SELECT events.pv_name FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  | id | select_type | table  | partitions | type | possible_keys    | key      | key_len | ref   | rows   | filtered | Extra                    |
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  |  1 | SIMPLE      | events | NULL       | ref  | PRIMARY,has_data | has_data | 1       | const | 267096 |    11.11 | Using where; Using index |
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  1 row in set, 1 warning (0.00 sec)
  
  mysql&gt; EXPLAIN SELECT events.pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  | id | select_type | table  | partitions | type | possible_keys    | key      | key_len | ref   | rows   | filtered | Extra                    |
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  |  1 | SIMPLE      | events | NULL       | ref  | PRIMARY,has_data | has_data | 1       | const | 267096 |    11.11 | Using where; Using index |
  +----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
  1 row in set, 1 warning (0.00 sec)
  
  
  SELECT events.pv_name FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  114480 rows in set (0.37 sec)
  
  SELECT events.pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  114480 rows in set (0.30 sec)
  
  
  mysql&gt; SHOW INDEXES FROM events;
  +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | events |          0 | PRIMARY  |            1 | pv_name     | A         |      422951 |     NULL | NULL   |      | BTREE      |         |               |
  | events |          0 | PRIMARY  |            2 | time_stamp  | A         |     4321990 |     NULL | NULL   |      | BTREE      |         |               |
  | events |          0 | has_data |            1 | pv_name     | A         |      240067 |     NULL | NULL   |      | BTREE      |         |               |
  | events |          0 | has_data |            2 | has_data    | A         |      436525 |     NULL | NULL   |      | BTREE      |         |               |
  | events |          0 | has_data |            3 | time_stamp  | A         |     4205163 |     NULL | NULL   |      | BTREE      |         |               |
  +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  5 rows in set (0.00 sec)
  
  mysql&gt; EXPLAIN SELECT events.pv_name FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
  | id | select_type | table  | partitions | type  | possible_keys    | key      | key_len | ref  | rows    | filtered | Extra                    |
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
  |  1 | SIMPLE      | events | NULL       | index | PRIMARY,has_data | has_data | 251     | NULL | 4462633 |     1.11 | Using where; Using index |
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
  1 row in set, 1 warning (0.00 sec)
  
  mysql&gt; EXPLAIN SELECT events.pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
  | id | select_type | table  | partitions | type  | possible_keys    | key      | key_len | ref  | rows   | filtered | Extra                                 |
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
  |  1 | SIMPLE      | events | NULL       | range | PRIMARY,has_data | has_data | 251     | NULL | 240076 |   100.00 | Using where; Using index for group-by |
  +----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
  1 row in set, 1 warning (0.00 sec)
  
  SELECT events.pv_name FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  114480 rows in set (6.79 sec)
  
  SELECT events.pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp &gt; 0 AND events.time_stamp &lt; 9999999999999999999 GROUP BY events.pv_name;
  114480 rows in set (2.65 sec)
  </code></pre>