title: Improve DATETIME grouping queries performance
body: <p>This is my first attempt at reaching out for help on StackOverflow :)</p>
  
  <p>I have the following table:</p>
  
  <pre><code>CREATE TABLE `tinfinite_visits` (
    `visit_id` int(255) NOT NULL AUTO_INCREMENT,
    `identity_id` int(255) NOT NULL,
    `ip` varchar(39) NOT NULL,
    `loggedin` enum('0','1') NOT NULL DEFAULT '0',
    `url` longtext NOT NULL,
    `realurl` longtext NOT NULL,
    `referrer` longtext NOT NULL,
    `method` enum('GET','POST','HEAD','OPTIONS','PUT','DELETE','TRACE','CONNECT','PATCH') NOT NULL,
    `client` longtext NOT NULL,
    `referring` longtext NOT NULL,
    `timestart` datetime NOT NULL,
    `timeend` datetime NOT NULL,
    PRIMARY KEY (`visit_id`),
    KEY `timestart` (`timestart`),
    KEY `identity_id` (`identity_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>At some point, I need to get some data from this table to generate a line chart. I currently do it using 5 different queries, for 5 different time intervals (total, year, month, week, day).</p>
  
  <p>The table currently has around 200.000 rows, but will got a lot bigger (even tens of millions records).</p>
  
  <p>While my queries work perfectly for the purpose, I'm trying to find a better way performance-wise.</p>
  
  <p>So I would enormously appreciate any tips/advice on how can I improve the queries' performance, preferably even merging all 5 queries into 1, if possible.</p>
  
  <p>The queries I'm currently using, their EXPLAINs, as well as their execution times (for around 200.000 rows) are as follows:</p>
  
  <p><strong>Day query:</strong></p>
  
  <pre><code>SELECT COUNT(DISTINCT(`identity_id`)) AS visits, DATE_FORMAT(CONVERT_TZ(timestart, '-5:00', '+3:00'), '%l%p') AS unit
    FROM tinfinite_visits
    WHERE `timestart` &gt;= DATE_SUB(NOW(), INTERVAL 24 HOUR)
    GROUP BY unit
    ORDER BY `timestart` ASC
  </code></pre>
  
  <p>Explain:</p>
  
  <pre><code>+----+-------------+----------------------+-------+---------------+-----------+---------+-----+-------+------------------------------+
  | id | select_type | table                | type  | possible_keys | key       | key_len | ref | rows  | Extra                        |
  +----+-------------+----------------------+-------+---------------+-----------+---------+-----+-------+------------------------------+
  | 1  | SIMPLE      | tinfinite_visits     | range | timestart     | timestart | 8       |     | 11113 | Using where; Using temporary |
  +----+-------------+----------------------+-------+---------------+-----------+---------+-----+-------+------------------------------+
  </code></pre>
  
  <p><em>Time: 0.011280059814453</em></p>
  
  <p><strong>Week query:</strong></p>
  
  <pre><code>SELECT COUNT(DISTINCT(`identity_id`)) AS visits, DATE_FORMAT(CONVERT_TZ(timestart, '-5:00', '+3:00'), '%a') AS unit
    FROM tinfinite_visits
    WHERE `timestart` &gt;= DATE_SUB(NOW(), INTERVAL 7 DAY)
    GROUP BY unit
    ORDER BY `timestart` ASC
  </code></pre>
  
  <p>Explain:</p>
  
  <pre><code>+----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  | id | select_type | table            | type | possible_keys | key | key_len | ref | rows   | Extra                                        |
  +----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  | 1  | SIMPLE      | tinfinite_visits | ALL  | timestart     |     |         |     | 205897 | Using where; Using temporary; Using filesort |
  +----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  </code></pre>
  
  <p><em>Time: 0.13543295860291</em></p>
  
  <p><strong>Month query:</strong></p>
  
  <pre><code>SELECT COUNT(DISTINCT(`identity_id`)) AS visits, DATE_FORMAT(CONVERT_TZ(timestart, '-5:00', '+3:00'), '%d') AS unit
    FROM tinfinite_visits
    WHERE `timestart` &gt;= DATE_SUB(NOW(), INTERVAL 28 DAY)
    GROUP BY unit
    ORDER BY `timestart` ASC
  </code></pre>
  
  <p>Explain:</p>
  
  <pre><code>+----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  | id | select_type | table            | type | possible_keys | key | key_len | ref | rows   | Extra                                        |
  +----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  | 1  | SIMPLE      | tinfinite_visits | ALL  | timestart     |     |         |     | 205897 | Using where; Using temporary; Using filesort |
  +----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  </code></pre>
  
  <p><em>Time: 0.21460795402527</em></p>
  
  <p><strong>Year query:</strong></p>
  
  <pre><code>SELECT COUNT(DISTINCT(`identity_id`)) AS visits, DATE_FORMAT(`timestart`, '%b') AS unit
    FROM tinfinite_visits
    WHERE `timestart` &gt;= DATE_SUB(NOW(), INTERVAL 1 YEAR)
    GROUP BY unit
    ORDER BY `timestart` ASC
  </code></pre>
  
  <p>Explain:</p>
  
  <pre><code>+----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  | id | select_type | table            | type | possible_keys | key | key_len | ref | rows   | Extra                                        |
  +----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  | 1  | SIMPLE      | tinfinite_visits | ALL  | timestart     |     |         |     | 205897 | Using where; Using temporary; Using filesort |
  +----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  </code></pre>
  
  <p><em>Time: 0.50977802276611</em></p>
  
  <p><strong>Overall query:</strong></p>
  
  <pre><code>SELECT COUNT(DISTINCT(`identity_id`)) AS visits, DATE_FORMAT(`timestart`, '%b') AS unit
    FROM tinfinite_visits
    WHERE `timestart` &gt;= DATE_SUB(NOW(), INTERVAL 100 YEAR)
    GROUP BY unit
    ORDER BY `timestart` ASC
  </code></pre>
  
  <p>Explain:</p>
  
  <pre><code>+----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  | id | select_type | table            | type | possible_keys | key | key_len | ref | rows   | Extra                                        |
  +----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  | 1  | SIMPLE      | tinfinite_visits | ALL  | timestart     |     |         |     | 205897 | Using where; Using temporary; Using filesort |
  +----+-------------+------------------+------+---------------+-----+---------+-----+--------+----------------------------------------------+
  </code></pre>
  
  <p><em>Time: 0.52196192741394</em></p>
  
  <p>Thank you very, very much!</p>