Let's look at this starting with your inner query:
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 are looking for equality match on username and range matching on acctstarttime . Then you use acctuniqueid to group and pull the extreme value ( MIN() ) from radacctid .
Therefore, to speed up this subquery, you will need the following composite index.
(username, acctstarttime, acctuniqueid, radacctid)
How it works? Think of an index (these are BTREE indices) as a sorted list of values in it.
- The query engine randomly accesses the list — quickly, O (log (n)) —to find the first entry matching
username and the lower end of your BETWEEN range. - Then it sequentially scans the list, recording by recording, until it reaches the upper level of the
BETWEEN range. This is called index range scanning. - When scanning, it searches for each new
acctuniqueid, value acctuniqueid, in order, and then takes the lowest value - first in order - radacctid , and then moves on to the next accuniqueid is called index scanning, and it is wonderfully cheap.
So add this composite index. This is likely to greatly affect the performance of your request.
An external query is as follows.
SELECT sum(acctinputoctets) as upload, sum(acctoutputoctets) as download FROM radacct a INNER JOIN ( ) b ON a.acctuniqueid = b.acctuniqueid AND a.radacctid = b.radacctid
To do this, you need a composite coverage index.
(acctuniqueid, radacctid, acctinputoctets, acctoutputoctets)
This part of the request is also satisfied with the index mask.
- The first two columns in the index allow you to search for each row you want based on the internal result of the query.
- The query engine can then scan the index, adding up the values of the other two columns.
(This is called a coverage index because it contains some columns that are present only because we want their values, and not because we want to index them. Some other DBMS models and models allow you to include additional columns in indexes without making them available for searching. It's a little cheaper, especially with INSERT operations. MySQL doesn't.)
So, your first element of action: add these two composite indexes and retry the query.
For your question, it looks like you have placed many single-column indexes on your table in the hope that they will speed things up. This is the notorious antipattern in database design. Sincerely, you must get rid of any indexes that you do not need. They do not help in queries, and they slow down INSERTS . This is your second element of action.
Third, read this http://use-the-index-luke.com/ This is very useful.
Pro tip: did you see how I formatted your request? Developing a personal formatting agreement that clearly displays tables, columns, ON clauses, and other aspects of a query is extremely important when you need to understand this.