title: How to improve my tables and queries for Big Data applications?
body: <p>I created an API on Symfony which produces more than 1 million entries by day into one of the MySql tables. This table structure is defined this way:</p>
  
  <p><a href="https://i.stack.imgur.com/Ocgfh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ocgfh.png" alt="enter image description here"></a></p>
  
  <p>After some weeks of use, the table has already 35 million (not trillion) of rows. And when I query this table, the response time is almost 20 seconds for a simple query like this one:</p>
  
  <pre><code>public function findAllCryptosByRank($date_minute)
  {
      $query = $this-&gt;_em-&gt;createQueryBuilder()
          -&gt;select("cm")
          -&gt;from("APIBundle:CoinmarketcapSnapshot", "cm")
          -&gt;where("cm.date_minute = :date_minute")
          -&gt;orderBy("cm.rank", "ASC")
          -&gt;setMaxResults(10)
          -&gt;setParameters(array(
              'date_minute' =&gt; $date_minute,
          ));
      $finalQuery = $query-&gt;getQuery();
      return $finalQuery-&gt;getArrayResult();
  }
  </code></pre>
  
  <p>When doing something more complicated, it's even worse; the queries take more than one minute. For example for something like that:</p>
  
  <pre><code>public function findAllCryptosByRank($date_minute,$date_hour,$date_day,$date_month,$date_year)
  {
      $query = $this-&gt;_em-&gt;createQueryBuilder()
          -&gt;select("cm", "c.logo", "c.title")
          -&gt;from("APIBundle:CoinmarketcapSnapshot", "cm")
          -&gt;where("cm.date_minute = :date_minute")
          -&gt;andWhere("cm.date_hour = :date_hour")
          -&gt;andWhere("cm.date_day = :date_day")
          -&gt;andWhere("cm.date_month = :date_month")
          -&gt;andWhere("cm.date_year = :date_year")
          -&gt;leftJoin(
              'APIBundle:Cryptocurrency',
              'c',
              \Doctrine\ORM\Query\Expr\Join::WITH,
              'cm.cryptocurrency__id = c. coinmarketcap_id'
          )
          -&gt;orderBy("cm.rank", "ASC")
          -&gt;setMaxResults(10)
          -&gt;setParameters(array('date_minute'=&gt;$date_minute,'date_hour'=&gt;$date_hour,'date_day'=&gt;$date_day,'date_month'=&gt;$date_month,'date_year'=&gt;$date_year))
      ;
      $finalQuery = $query-&gt;getQuery();
      return $finalQuery-&gt;getArrayResult();
  }
  </code></pre>
  
  <p>So, what can I do to hugely improve these performance? I read about Doctrine, that was not designed for Big Data use cases. </p>
  
  <p>I know it's possible to improve MySQL performance by using tools such has Hadoop, or by optimising indexes for example. </p>
  
  <p>But will this be enough regarding the very low performances at this moment?</p>
  
  <p>I want to be sure Symfony is the good choice for this application. I am considering migrating the API to another Backend Framework, such as ASP.NET or Node.JS. What do you think?</p>