Hi, I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic data :
record_time datetime - time when recording was added ip char(15) - ip that generated the traffic type tinyint(3) - traffic type ( 1 - local, 2 - internet ) inbound int(10) - in bytes outbound int(10) - out bytes Records are inserted each 5 minutes through a cron script. Currently there are 3,330,367 rows. Primary index is defined on ( ip, type, record_time ), columns in that order. Also there is an index defined only on record_time Now for an example to get traffic for this month, I use : SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out` FROM `accounting` WHERE `record_time` BETWEEN 20060401000000 AND 20060413160000 GROUP BY `ip`,`type` this query takes aprox 7 seconds Using EXPLAIN gives : select_type table type possible_keys key key_len ref rows Extra SIMPLE accounting range record_time record_time 8 NULL 362410 Using where; Using temporary; Using filesort If I remove the SUM functions I am getting also "Using index in group-by" and the query takes only 0.25 sec Is there anyway to optimize this query to get faster responses ? Thanks, ----------------------- Puiu Hrenciuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
