Sum optimization and indexing in mysql

Sum optimization and indexing in mysql

// Create table if not exists 'radacct '('radacctid' bigint (21) not null AUTO_INCREMENT, 'username' varchar (64) not null default '', 'acctsessiontime' int (12) default null, 'acctinputoctets 'bigint (12) default null, 'acctoutputoctets' bigint (12) 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 (AcctOutputOctets) AS Download, sum (AcctInputOctets + AcctOutputOctets) AS Bandwidth FROM radacct group by username '; mysql> explain SELECT UserName, count (*) AS numOfSession, sum (AcctSessionTime) AS Time, sum (aggregate) AS Upload, sum (AcctOutputOctets) AS Download, sum (AcctInputOctets + AcctOutputOctets) AS Bandwidth FROM radacct group by UserName; + ---- + ------------- + --------- + ------ + ------------- + ------ + --------- + ------ + -------- + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + --------- + ------ + --------------- + ------ + --------- + ------ + -------- + Upper + | 1 | SIMPLE | radacct | ALL | NULL | 456010 | Using temporary; using filesort | + ---- + ------------- + --------- + ------ + --------------- + ------ + --------- + ------ + -------- + --------------------------------- +

Adding an index to the sum field cannot improve the query efficiency.

Adding pagination or something slows down.

How to optimize it? thank you first.

Reply to discussion (solution)

First, if the calculation is used, the index will no longer work. Therefore, adding or not adding an index does not work.

My idea is this. if the table becomes extraordinary, you can cache a copy of data instead of each query.

First, if the calculation is used, the index will no longer work. Therefore, adding or not adding an index does not work.

My idea is this. if the table becomes extraordinary, you can cache a copy of data instead of each query.
Okay, cool. no one else will give you all the points.

Adding pagination slows down my thinking. if the table becomes extraordinary, you can consider caching a copy of data instead of each query.