MySQL slow avg min max using filesort

Question

How can I optimize the query, it takes about 2 seconds, with about 500 thousand records in the table "resulta", but I expect touble to grow to seven billion.

SELECT hopcount, hop, round( avg( rtt ) , 2 ) AS avg, min( rtt ) AS min, max( rtt ) AS max FROM results JOIN traces ON id = trace WHERE target =9 AND rtt > -1 GROUP BY hop` 

Explain the conclusion:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE traces ref PRIMARY,fk_traces_1_idx fk_traces_1_idx 5 const 26333 Using where; Using temporary; Using filesort 1 SIMPLE results ref trace trace 5 pinger.traces.id 7 Using where 

Tables:

 CREATE TABLE IF NOT EXISTS `results` ( `hop` int(11) DEFAULT NULL, `trace` int(11) DEFAULT NULL, `rtt` int(11) NOT NULL, `seq` int(11) NOT NULL, KEY `trace` (`trace`), KEY `fk_hops_id` (`hop`), KEY `seq` (`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `traces` ( `id` int(11) NOT NULL AUTO_INCREMENT, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `hopcount` smallint(6) NOT NULL, `target` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_traces_1_idx` (`target`,`id`,`time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=52308; 

Answer 1

MySQL GROUP BY implies ORDER BY

To remove this implicit view, add ORDER BY NULL

This is indicated in MySQL Docs for SELECT.

If you use GROUP BY, the output rows are sorted according to the GROUP BY columns, as if you had ORDER BY for the same columns. To avoid the sorting overhead that GROUP BY does, add ORDER BY NULL:

And also in ORDER BY Optimization