title: Accelerate large mysql join
body: <p>I am writing a sql to list every day active user with its first appearance date in the log table. The <em>MySQL version is 5.7</em>.</p>
  
  <p>Like:</p>
  
  <pre><code>date           active_users    reg_date
  2020-03-1        user1        2019-02-01
  2020-03-1        user2        2019-03-04
  2020-03-2        user3        2019-01-18
  2020-03-2        user1        2019-02-01
  </code></pre>
  
  <p>I have finished a query to achieve this, but as shown, I made 2 aggregation for the same table and then join them together... The login log table <code>game_user_log</code> comprises <code>2 million</code> rows of data and I have added index on column <code>data_date</code> and <code>data_date</code>, but my query takes about <code>1 minute</code> .</p>
  
  <p>Is there any way to optimize and accelerate the query? Any help is appreciated.</p>
  
  <p>This is my query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT a.data_date, a.user_id, b.reg_date 
                -- List every day and de-duplicated users
  from ( SELECT distinct data_date, user_id 
         from `game_user_log`) a
                -- Get the first login date as reg_date
  left outer join ( SELECT user_id, min(data_date) reg_date 
                    FROM `game_user_log` 
                    GROUP BY user_id) b
      on a.user_id=b.user_id
  </code></pre>