optimization and indexing problems for sum in MySQL

Table structure CREATE TABLE IF not EXISTS ' Radacct ' (' Radacctid ' bigint (+) not NULL auto_increment, ' UserName ' varchar ($) Not N ULL default ' ', ' acctsessiontime ' int (+) default NULL, ' Acctinputoctets ' bigint () default null, ' Acctoutputoctets ' B  Igint () DEFAULT NULL, ..... PRIMARY key (' Radacctid '), key ' UserName ' (' UserName '), key ' Acctsessiontime ' (' acctsessiontime '), key ' Acctinputoctets ' (' acctinputoctets '), KEY ' acctoutputoctets ' (' acctoutputoctets ')) Engine=myisam DEFAULT Charset=utf8 collate=utf8_ Unicode_ci auto_increment=456017; $sql = ' SELECT UserName, COUNT (*) as numofsession, sum (acctsessiontime) as time, sum (acctinputoctets) as Upload, sum (accto  Utputoctets) as Download, sum (acctinputoctets+acctoutputoctets) as Bandwidth from Radacct GROUP by UserName '; Mysql> explain SELECT UserName, COUNT (*) as numofsession, sum (acctsessiontime) as time, sum (acctinputoctets) as UPL Oad, SUM (acctoutputoctets) as Download, sum (acctinputoctets + acctoutputoctets) as BaNdwidth from Radacct GROUP by UserName; +----+-------------+---------+------+---------------+------+---------+------+--------+------------------------- --------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+--------+---  ------------------------------+| 1 | Simple | Radacct | All | NULL | NULL | NULL | NULL | 456010 | Using temporary; Using filesort |+----+-------------+---------+------+---------------+------+---------+------+--------+---------- -----------------------+

The amount of data is about 45w. Adding an index to the sum field does not improve query efficiency

Plus paging or something is even slower.

How to optimize the comparison good AH first humbly

Reply to discussion (solution)

First of all, if you use the calculation, the index will be invalidated, so you do not add index no effect.

My idea is this, if the table becomes extraordinary, you can consider caching a copy of the data to replace your query every time.

First of all, if you use the calculation, the index will be invalidated, so you do not add index no effect.

My idea is this, if the table becomes extraordinary, you can consider caching a copy of the data to replace your query every time.
Okay, no one's going to give you all the points for the year.

Plus paging or something is even slower. My idea is this, if the table becomes extraordinary, you can consider caching a copy of the data in lieu of your query every time.

