title: mysql query taking more than 30 seconds
body: <p>Below is the output of "show full processlist"  on my-sql command line. this query calculates the users total upload and download bandwidth for specified period of time.  (the database is from freeradius). How can i make the query faster.  [ Memory:150G , processr:8 centos 6.8]</p>
  
  <pre><code>15260415|radiusremote|panel.example.com:57526|radius|Query|35|Copying to tmp table  
  
  SELECT  sum(acctinputoctets) as upload,sum(acctoutputoctets) as download
      FROM  radacct a
      INNER JOIN  
          ( SELECT  acctuniqueid, MIN( radacctid ) radacctid
              FROM  radacct
              WHERE  username='nyjohan'
                and  acctstarttime between '2016-01-15 13:50:05'
                                       AND '2016-08-07 13:16:36'
              GROUP BY  acctuniqueid
          ) b  ON a.acctuniqueid = b.acctuniqueid
             AND  a.radacctid = b.radacctid 
  </code></pre>
  
  <p>There are indexes created on the table , below is the output of indexes on table</p>
  
  <pre><code>mysql&gt; show index from radacct;
  +---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
  | Table   | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
  | radacct |          0 | PRIMARY         |            1 | radacctid       | A         |   161791738 |     NULL | NULL   |      | BTREE      |         |
  | radacct |          1 | username        |            1 | username        | A         |          15 |     NULL | NULL   |      | BTREE      |         |
  | radacct |          1 | framedipaddress |            1 | framedipaddress | A         |      458333 |     NULL | NULL   |      | BTREE      |         |
  | radacct |          1 | acctsessionid   |            1 | acctsessionid   | A         |   161791738 |     NULL | NULL   |      | BTREE      |         |
  | radacct |          1 | acctsessiontime |            1 | acctsessiontime | A         |       46332 |     NULL | NULL   | YES  | BTREE      |         |
  | radacct |          1 | acctuniqueid    |            1 | acctuniqueid    | A         |   161791738 |     NULL | NULL   |      | BTREE      |         |
  | radacct |          1 | acctstarttime   |            1 | acctstarttime   | A         |    40447934 |     NULL | NULL   | YES  | BTREE      |         |
  | radacct |          1 | acctstoptime    |            1 | acctstoptime    | A         |    80895869 |     NULL | NULL   | YES  | BTREE      |         |
  | radacct |          1 | nasipaddress    |            1 | nasipaddress    | A         |          15 |     NULL | NULL   |      | BTREE      |         |
  +---------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
  </code></pre>
  
  <p>Schema of table</p>
  
  <pre><code>mysql&gt; describe radacct;
  +----------------------+-------------+------+-----+---------+----------------+
  | Field                | Type        | Null | Key | Default | Extra          |
  +----------------------+-------------+------+-----+---------+----------------+
  | radacctid            | bigint(21)  | NO   | PRI | NULL    | auto_increment |
  | acctsessionid        | varchar(32) | NO   | MUL |         |                |
  | acctuniqueid         | varchar(32) | NO   | MUL |         |                |
  | username             | varchar(64) | NO   | MUL |         |                |
  | groupname            | varchar(64) | NO   |     |         |                |
  | realm                | varchar(64) | YES  |     |         |                |
  | nasipaddress         | varchar(15) | NO   | MUL |         |                |
  | nasportid            | varchar(15) | YES  |     | NULL    |                |
  | nasporttype          | varchar(32) | YES  |     | NULL    |                |
  | acctstarttime        | datetime    | YES  | MUL | NULL    |                |
  | acctstoptime         | datetime    | YES  | MUL | NULL    |                |
  | acctsessiontime      | int(12)     | YES  | MUL | NULL    |                |
  | acctauthentic        | varchar(32) | YES  |     | NULL    |                |
  | connectinfo_start    | varchar(50) | YES  |     | NULL    |                |
  | connectinfo_stop     | varchar(50) | YES  |     | NULL    |                |
  | acctinputoctets      | bigint(20)  | YES  |     | NULL    |                |
  | acctoutputoctets     | bigint(20)  | YES  |     | NULL    |                |
  | calledstationid      | varchar(50) | NO   |     |         |                |
  | callingstationid     | varchar(50) | NO   |     |         |                |
  | acctterminatecause   | varchar(32) | NO   |     |         |                |
  | servicetype          | varchar(32) | YES  |     | NULL    |                |
  | framedprotocol       | varchar(32) | YES  |     | NULL    |                |
  | framedipaddress      | varchar(15) | NO   | MUL |         |                |
  | acctstartdelay       | int(12)     | YES  |     | NULL    |                |
  | acctstopdelay        | int(12)     | YES  |     | NULL    |                |
  | xascendsessionsvrkey | varchar(10) | YES  |     | NULL    |                |
  +----------------------+-------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Explain output:-</p>
  
  <pre><code>explain SELECT sum(acctinputoctets) as upload,sum(acctoutputoctets) as download
  FROM radacct a
  INNER JOIN (
  SELECT acctuniqueid, MIN( radacctid ) radacctid
  FROM radacct
  WHERE username='dave137' and acctstarttime between '2016-08-03 00:00:00' and '2016-08-07 14:47:54' GROUP BY acctuniqueid
  )b ON a.acctuniqueid = b.acctuniqueid
  AND a.radacctid = b.radacctid ;
  
  +----+-------------+------------+--------+------------------------+----------+---------+-------------+-------+----------------------------------------------+
  | id | select_type | table      | type   | possible_keys          | key      | key_len | ref         | rows  | Extra                                        |
  +----+-------------+------------+--------+------------------------+----------+---------+-------------+-------+----------------------------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt; | ALL    | NULL                   | NULL     | NULL    | NULL        |    10 |                                              |
  |  1 | PRIMARY     | a          | eq_ref | PRIMARY,acctuniqueid   | PRIMARY  | 8       | b.radacctid |     1 | Using where                                  |
  |  2 | DERIVED     | radacct    | ref    | username,acctstarttime | username | 66      |             | 10164 | Using where; Using temporary; Using filesort |
  +----+-------------+------------+--------+------------------------+----------+---------+-------------+-------+----------------------------------------------+
  3 rows in set (9.91 sec)
  </code></pre>