title: How to optimize data aggregation in day of week, time of day for a period of one month back
body: <p>Here is the situation:</p>
  
  <p>I have a SAAS Application which is a simple RSS Feed reader. I think most people know what this is - users subscribing to RSS feeds and then reading items from them. Nothing new. One feed can have many subscribers.</p>
  
  <p>I've implemented some statistics for the users, but I don't think I've chosen the right approach, because things are getting slower by the hour as the number of users and feeds grows.</p>
  
  <p>Here's what I'm doing now:</p>
  
  <ol>
  <li><p>At every hour get the total number of articles for each feed:</p>
  
  <pre><code>SELECT COUNT(*) FROM articles WHERE feed_id=?
  </code></pre></li>
  <li><p>Get the previous value to calculate the delta (this is getting a little slow):</p>
  
  <pre><code>SELECT value FROM feeds_stats WHERE feed_id=? AND name='total_articles' ORDER BY date DESC LIMIT 1
  </code></pre></li>
  <li><p>Insert the new value and delta:</p>
  
  <pre><code>INSERT INTO feeds_stats (date,feed_id,name,value,delta) VALUES ('".date("Y-m-d H:i:s",$global_timestamp)."','".$feed_id','total_articles','".$value."','".($value-$old_value)."')
  </code></pre></li>
  <li><p>For every user get his feeds and for each feed get the number of articles he has read:</p>
  
  <pre><code>SELECT COUNT(*) FROM users_articles ua JOIN articles a ON a.id=ua.article_id WHERE a.feed_id='%s' AND ua.user_id='%s' AND ua.read=1
  </code></pre></li>
  </ol>
  
  <p>users_articles is a table which holds the read state of each article per user</p>
  
  <ol start="5">
  <li><p>Then again get the delta:</p>
  
  <pre><code>SELECT value FROM users_feeds_stats WHERE user_id='?' AND feed_id='?' AND name='total_reads' ORDER BY date DESC LIMIT 1
  </code></pre></li>
  <li><p>And insert the new value + delta:</p>
  
  <pre><code>INSERT INTO users_feeds_stats (date,user_id,feed_id,name,value,delta) VALUES ('".date("Y-m-d H:i:s",$global_timestamp)."','".$user_id."','".$feed_id."','total_reads','".$value."','".($value-$old_value)."')
  </code></pre></li>
  </ol>
  
  <p><strong>When all feeds for the user has been processed comes the aggregation part</strong>:</p>
  
  <p>This is a bit tricky and I think there should be a lot of room for optimization here.
  Here is the actual aggregation function in PHP:</p>
  
  <pre><code>&lt;?php
  
  function aggregate_user_stats($user_id=false,$feed_id=false){
      global $global_timestamp;
      // defined dimensions
      $feed_types[0] = array("days_back" =&gt; 31, "group_by" =&gt; "DATE_FORMAT(date, '%Y-%m-%d')");
      $feed_types[1] = array("days_back" =&gt; 31, "group_by" =&gt; "WEEKDAY(date)+1");
      $feed_types[2] = array("days_back" =&gt; 31, "group_by" =&gt; "HOUR(date)");
  
      if($user_id){
          $where = " WHERE id=".$user_id;
      }
  
      $feed_where = "";
      $getusers = mysql_query("SELECT id FROM users".$where)or die(__LINE__." ".mysql_error());
      while($user = mysql_fetch_assoc($getusers)){
          if($feed_id){
              $feed_where = " AND feed_id=".$feed_id;
          }
  
          $user_feeds = array();
          $getfeeds = mysql_query("SELECT feed_id FROM subscriptions WHERE user_id='".$user["id"]."' AND active=1".$feed_where)or die(__LINE__." ".mysql_error());
          while($row = mysql_fetch_assoc($getfeeds)){
              foreach($feed_types as $tab =&gt; $type){
                  $getdata = mysql_query("
                  SELECT ".$type["group_by"]." AS date, name, SUM(delta) AS delta FROM feeds_stats WHERE feed_id = '".$row["feed_id"]."' AND name='total_articles' AND date &gt; DATE_SUB(NOW(), INTERVAL ".$type["days_back"]." DAY) GROUP BY name, ".$type["group_by"]." 
                  UNION 
                  SELECT ".$type["group_by"]." AS date, name, SUM(delta) AS delta FROM users_feeds_stats WHERE user_id = '".$user["id"]."' AND feed_id = '".$row["feed_id"]."' AND name='total_reads' AND date &gt; DATE_SUB(NOW(), INTERVAL ".$type["days_back"]." DAY) GROUP BY name, ".$type["group_by"]."
                  ")or die(__LINE__." ".mysql_error());
                  $data = array();
                  while($row = mysql_fetch_assoc($getdata)){
                      $data[$row["date"]][$row["name"]] = $row["delta"];
                  }
                  if(count($data)){
                      db_start_trx();
                      mysql_query("DELETE FROM stats_feeds_over_time WHERE feed_id='".$row["feed_id"]."' AND user_id='".$user["id"]."' AND tab='".$tab."'")or die(__LINE__." ".mysql_error());
                      foreach($data as $time =&gt; $keys){
                          mysql_query("REPLACE INTO stats_feeds_over_time (feed_id,user_id,tab,date,total_articles,total_reads,total_favs) VALUES ('".$row["feed_id"]."','".$user["id"]."','".$tab."','".$time."','".$keys["total_articles"]."','".$keys["total_reads"]."','".$keys["total_favs"]."')")or die(__LINE__." ".mysql_error());
                      }
                      db_commit_trx();
                  }
              }
          }
      }
  }
  </code></pre>
  
  <p>Some notes:</p>
  
  <p>Edit: Here are the DDL's of the involved tables:</p>
  
  <pre><code>CREATE TABLE `articles` (
    `id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `feed_id` INTEGER(11) UNSIGNED NOT NULL,
    `date` INTEGER(10) UNSIGNED NOT NULL,
    `date_updated` INTEGER(11) UNSIGNED NOT NULL,
    `title` VARCHAR(1000) COLLATE utf8_general_ci NOT NULL DEFAULT '',
    `url` VARCHAR(2000) COLLATE utf8_general_ci NOT NULL DEFAULT '',
    `author` VARCHAR(200) COLLATE utf8_general_ci NOT NULL DEFAULT '',
    `hash` CHAR(32) COLLATE utf8_general_ci NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    UNIQUE KEY `feed_id_hash` (`feed_id`, `hash`),
    KEY `date` (`date`),
    KEY `url` (`url`(255))
  )ENGINE=InnoDB
  AUTO_INCREMENT=0
  CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
  COMMENT='';
  
  
  CREATE TABLE `users_articles` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER(11) UNSIGNED NOT NULL,
    `article_id` INTEGER(11) UNSIGNED NOT NULL,
    `subscription_id` INTEGER(11) UNSIGNED NOT NULL,
    `read` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_id` (`user_id`, `article_id`),
    KEY `article_id` (`article_id`),
    KEY `subscription_id` (`subscription_id`)
  )ENGINE=InnoDB
  CHECKSUM=1 AUTO_INCREMENT=0
  CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
  COMMENT='';
  
  
  CREATE TABLE `feeds_stats` (
    `id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `feed_id` INTEGER(11) UNSIGNED NOT NULL,
    `date` DATETIME NOT NULL,
    `name` VARCHAR(50) COLLATE utf8_general_ci NOT NULL DEFAULT '',
    `value` INTEGER(11) NOT NULL,
    `delta` INTEGER(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `name` (`name`),
    KEY `feed_id` (`feed_id`),
    KEY `date` (`date`)
  )ENGINE=InnoDB
  AUTO_INCREMENT=0
  CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
  COMMENT='';
  
  
  CREATE TABLE `users_feeds_stats` (
    `id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
    `feed_id` INTEGER(11) UNSIGNED NOT NULL,
    `date` DATETIME NOT NULL,
    `name` VARCHAR(50) COLLATE utf8_general_ci NOT NULL DEFAULT '',
    `value` INTEGER(11) NOT NULL,
    `delta` INTEGER(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `name` (`name`),
    KEY `feed_id` (`feed_id`),
    KEY `user_id` (`user_id`),
    KEY `date` (`date`)
  )ENGINE=InnoDB
  AUTO_INCREMENT=0
  CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
  COMMENT='';
  
  CREATE TABLE `stats_feeds_over_time` (
    `feed_id` INTEGER(11) UNSIGNED NOT NULL,
    `user_id` INTEGER(11) NOT NULL,
    `tab` INTEGER(11) NOT NULL,
    `date` VARCHAR(30) COLLATE utf8_general_ci NOT NULL DEFAULT '',
    `total_articles` DOUBLE(9,2) UNSIGNED NOT NULL,
    `total_reads` DOUBLE(9,2) UNSIGNED NOT NULL,
    `total_favs` DOUBLE(9,2) UNSIGNED NOT NULL,
    PRIMARY KEY (`feed_id`, `user_id`, `tab`, `date`)
  )ENGINE=InnoDB
  AUTO_INCREMENT=0 
  CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
  COMMENT='';
  </code></pre>
  
  <p>In the end of the aggregation function there is a REPLACE in table stats_feeds_over_time. This table holds just the records, that will be displayed on the graph, so the actual graphing process does not involve heavy queries.</p>
  
  <p>Finally here are the graphs produced by this:</p>
  
  <p><img src="https://i.stack.imgur.com/U31IH.png" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/55sH9.png" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/X7Lqr.png" alt="enter image description here"></p>
  
  <p>I would be glad if someone point me in the right direction on where and how to optimize this solution, even if it means to ditch MySQL for statistics.</p>
  
  <p>I have long experience with <a href="http://oss.oetiker.ch/rrdtool/" rel="nofollow noreferrer">RRDTool</a>, but here the situation is different, because of the "Time of day", "Day of week" aggregations.</p>