title: Laravel scoreboard of more than 1 million users
body: <p>I'm working in a biggest application ( more than 1 million users ) and I try to get the ranking of each user in the scoreboard section but had this problem: <strong>the result is very very slow</strong></p>
  
  <p>This is the architecture of my database:</p>
  
  <pre><code>Schema::create('users', function (Blueprint $table) {
              $table-&gt;increments('id');
              ...
  });
  </code></pre>
  
  <hr>
  
  <pre><code>Schema::create('topics', function (Blueprint $table) {
              $table-&gt;increments('id');
              ...
  });
  </code></pre>
  
  <p>The topics table have than 20 row</p>
  
  <hr>
  
  <pre><code>    Schema::create('user_scores', function (Blueprint $table) {
          $table-&gt;increments('id');
          $table-&gt;integer('user_id')-&gt;unsigned();
          $table-&gt;integer('topic_id')-&gt;unsigned();
  
          $table-&gt;unique(['user_id', 'topic_id']);
  
          $table-&gt;float('timer');
          $table-&gt;integer('score');
       });
  </code></pre>
  
  <p><strong>The query to make rank for users</strong></p>
  
  <pre><code>        User::where('type',0)-&gt;get()-&gt;each(function ($user) {
              $user-&gt;topics= $user-&gt;scores-&gt;sum('score');
              $user-&gt;timing= $user-&gt;scores-&gt;sum('timer');
          })-&gt;sort(function ($a, $b){
            return  ($b-&gt;topics - $a-&gt;topics) == 0  
              ? ($a-&gt;timing - $b-&gt;timing) 
              : ($b-&gt;topics - $a-&gt;topics);
          })-&gt;values()-&gt;each(function($user, $key){
                  $user-&gt;rank = $key +1;
          });
  </code></pre>
  
  <p>Any optimization should I make the get the result quicker? Thanks.</p>