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]
