Let's work this out starting with your inner query, which is:
SELECT acctuniqueid,
MIN( radacctid ) radacctid
FROM radacct
WHERE username='batman215'
and acctstarttime between '2016-02-03 12:10:47'
and '2016-04-25 16:46:01'
GROUP BY acctuniqueid
You're looking for an equality match on username and a range match on acctstarttime. You're then using acctuniqueid to group and pulling an extreme value (MIN()) from radacctid.
Therefore, to accelerate this subquery, you need the following compound index.
(username, acctstarttime, acctuniqueid, radacctid)
How does this work? Think of an index (these are BTREE indexes) as a sorted list of the values in it.
- The query engine random-accesses the list -- fast, O(log(n)) -- to find the first entry matching
username and the low end of your BETWEEN range.
- It then sequentially scans the list, entry by entry, until it comes to the high end of the
BETWEEN range. That's called an index range scan.
- As it scans, it looks for each new value of
acctuniqueid, in order and then takes the lowest value -- the first one in order -- of radacctid, then skips ahead to the next value of accuniqueid. That's called a loose index scan and it's miraculously cheap.
So, add that compound index. That will probably make a big difference to your query performance.
Your outer query looks like this.
SELECT sum(acctinputoctets) as upload,
sum(acctoutputoctets) as download
FROM radacct a
INNER JOIN ( /*an aggregate
* yielding acctuniqueid and raddactid
* naturally ordered on those two columns
*/
) b ON a.acctuniqueid = b.acctuniqueid
AND a.radacctid = b.radacctid
For this you need the compound covering index
(acctuniqueid, radacctid, acctinputoctets, acctoutputoctets)
This part of the query is also satisfied with index magic.
- The first two columns in the index allow the lookup of each row you need, based on the inner query's result.
- The query engine can then scan the index adding up the other two columns' values.
(This is called a covering index because it contains some columns that are present just because we want their values, not because we want them indexed. Some other makes and models of DBMS allow extra columns to be included in indexes without making them searchable. That's a little cheaper, especially on INSERT operations. MySQL doesn't do that. )
So, your first action item: add these two compound indexes and retry your query.
It looks like, from your question, that you have placed a lot of single-column indexes on your table in the hopes they will speed things up. That's a notorious antipattern in database design. With respect, you should get rid of any indexes you don't know you need. They don't help queries and they slow down INSERTS. That's your second action item.
Third, go read this http://use-the-index-luke.com/ It's very helpful.
Pro tip: did you see how I formatted your query? Developing a personal formatting convention that clearly shows tables, columns, ON conditions, and other aspects of a query is tremendously important when you have to understand one.
