Sensor Database Structure

[s8utt]

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

[Digit]

That's a lot of rows, seconds and fields..

I have a data table which holds historic data for 111 sensors in a total of 509000 rows in >206 MB data space.
I can do 64 queries in 0.03 seconds, including 5 queries for gas, water,power, hot & cold water usage in the last 24 hours.

So I guess there's room for some improvement

1. You should be able to do without the redundant hour,minute,day,month,year fields. Saves you a lot of space.
2. Store the readings in the resolution you really need. As in: store 1 row per hour, half hour or 5 minutes depending on what you really need. For example, for water usage I have 1 row per hour - see what you can do with 30000 rows...
3. did you create indexes so the query doesn't have to read the whole table? An index starting with sensorid would be a good start. Run your query in some sort of profiler/tuning advisor and have a look at the suggestions it comes up with. (MySql will probably have something like this but I'm not sure)

[s8utt]

I guess I've not put much thought into it when I set it up.

I have 3 sensor rows every 30 seconds.

So the table has we'll say the maximum resolution, which gives the flexibility that I can dig down to any detail but its not cpu friendly to get what I want when using max, sums etc.

Would a good option be to create some more tables

1 to hourly
1 for daily
1 for weekly
1 for monthly

I then run a cron job / scheduled task to populate these tables from the master dump table ?
This would speed up the table queries while maintaining maximum resolution

[Digit]

That's an option. Although this adds redundancy again.
And you have to ask yourself what use there is in being able to see what the temperature was @ 03-03-2011 14:23:30...
The relevance of that rapidly decreases as time goes by; I've been there
Since you're the one in charge of the database you can do whatever you like; I would just choose what fits you best - cause there's no such thing as a "one structure fits all" database

[s8utt]

I take your point of the data relevancy.

I'm always a back both horses kind of guy. So for the moment the data redundancy doesn't both me so much

I think I'll set up multiple tables for quicker reference, then purge the main database for information older than 6 months. Try to get a balance

Cheers Digit.