title: How to handle millions of records daily with mysql
body: <p>I have a table in mysql like this</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `connections` (
    `src` int(10) unsigned NOT NULL,
    `sport` smallint(5) unsigned NOT NULL,
    `dst` int(10) unsigned NOT NULL,
    `dport` smallint(5) unsigned NOT NULL,
    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`src`,`sport`,`dst`,`dport`,`time`),
    KEY `time` (`time`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>2.5 million records daily inserted in this table.</p>
  
  <p>When i want to select records for a period of time like a day. it takes about 7 minutes.
  how can i improve it.</p>
  
  <p>i`m using ruby on rails version 4.0.0 </p>
  
  <p>My selection is like this</p>
  
  <pre><code>connections = Connection.select('src, dst, UNIX_TIMESTAMP(time) as time')
                    .where(time: timeFrom..timeTo)
                    .order('time ASC')
  </code></pre>
  
  <p>After selection from database i have a loop like this :</p>
  
  <pre><code>connections.each do |con|
  
          link = getServerID(con['src'])
          link = getServerID(con['dst']) if link == 0
  
          @total[link].append [con['time'] * 1000, con['dst']]
  end
  </code></pre>
  
  <p>in this loop i have a bit process on src and dst then i add it to a hash
  this sections takes along and my computer crashed</p>