title: MySQL Date Range Query Optimization
body: <p>I have a MySQL table structured like this:</p>
  <pre><code>CREATE TABLE `messages` (
    `id` int NOT NULL AUTO_INCREMENT,
    `author` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
    `message` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL,
    `serverid` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
    `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `guildname` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`,`date`)
  ) ENGINE=InnoDB AUTO_INCREMENT=27769461 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  </code></pre>
  <p>I need to query this table for various statistics using date ranges for Grafana graphs, however all of those queries are extremely slow, despite the table being indexed using a composite key of id and date.
  &quot;id&quot; is auto-incrementing and date is also always increasing.</p>
  <p>The queries generated by Grafana look like this:</p>
  <pre><code>SELECT
    UNIX_TIMESTAMP(date) DIV 120 * 120 AS &quot;time&quot;,
    count(DISTINCT(serverid)) AS &quot;servercount&quot;
  FROM messages
  WHERE
    date BETWEEN FROM_UNIXTIME(1615930154) AND FROM_UNIXTIME(1616016554)
  GROUP BY 1
  ORDER BY UNIX_TIMESTAMP(date) DIV 120 * 120
  </code></pre>
  <p>This query takes over 30 seconds to complete with 27 million records in the table.
  Explaining the query results in this output:</p>
  <pre><code>+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------------+
  | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                       |
  +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------------+
  |  1 | SIMPLE      | messages | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 26952821 |    11.11 | Using where; Using filesort |
  +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-----------------------------+
  </code></pre>
  <p>This indicates that MySQL is indeed using the composite primary key I created for indexing the data, but still has to scan almost the entire table, which I do not understand. How can I optimize this table for date range queries?</p>