Hmmm, I have omited that : I also have an index on (ip,type) in that order
"Barry" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Puiu Hrenciuc wrote: >> 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 > Set an index on ip and type and "probably" also on record_time > > Barry > > -- > Smileys rule (cX.x)C --o(^_^o) > Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
