Sensor Database Structure
Posted: Wed Sep 21, 2011 8:33 pm
I know there are quite a few that monitor energy usage I'm interested on your database layout.
I'm using mysql 5 and I have a single table that logs 3 sensors
fields
uniqueid = self explanitory
sensorid = self explanitory
reading = counter reading
date = unix timestampe
hour = 2 digit integer
minute = 2 digit integer
day = 2 digit integer
month = 2 digit integer
year = 4 digit integer
last = integer, number of counts since last reading
mreading = what the reading will be on the actual electricity meter
if I want to see the last 24 hours grouped per hour I run the following query
select hour, max(mreading)-min(mreading) as total from sensors where sensor=00 group by year,month,day,hour order by date desc limit 24
My table is about 150mb in size now with about 2.5million rows. The query takes about 7 seconds to run.
Has anyone got any recommendations how to speed up accessing this data quickly ?
Cheers
Scott
I'm using mysql 5 and I have a single table that logs 3 sensors
fields
uniqueid = self explanitory
sensorid = self explanitory
reading = counter reading
date = unix timestampe
hour = 2 digit integer
minute = 2 digit integer
day = 2 digit integer
month = 2 digit integer
year = 4 digit integer
last = integer, number of counts since last reading
mreading = what the reading will be on the actual electricity meter
if I want to see the last 24 hours grouped per hour I run the following query
select hour, max(mreading)-min(mreading) as total from sensors where sensor=00 group by year,month,day,hour order by date desc limit 24
My table is about 150mb in size now with about 2.5million rows. The query takes about 7 seconds to run.
Has anyone got any recommendations how to speed up accessing this data quickly ?
Cheers
Scott