Page 1 of 1

Mysql database structure

Posted: Wed Aug 29, 2012 11:36 pm
by psmaaswinkel
From THE beginnend of this year I am logging my homeseer sensorvalues in a mysql database for website usage.
THE structure is straight forward at this moment. I am using à single table with the devicegroup, devicenumber, time and value.

Because I like to keep things efficient I would like to know what is the best structure for storing the data. I don't feel the current layout is the best choice...

Any ideas?

Re: Mysql database structure

Posted: Thu Aug 30, 2012 12:04 am
by Digit
Why do you think your current setup is not the best choice and what do you want to improve?
It's a bit too vague to me to come up with ideas.
For instance, when I see devicegroup & devicenumber, I wonder if it's only the combination of the 2 that makes a row in the table relate to a specific sensor, or does the devicenumber already imply a devicegroup? Or, IOW, is the devicenumber unique? And do you carefully pick the right time interval between sensorvalues you store; for example, it would not be smart to store every sensorvalue (change) received from an Oregon Scientific Temp/Hum sensor (almost every 40 seconds).
So tell a bit more about your current layout, that will help to get useful replies (not like this one :wink: )

Re: Mysql database structure

Posted: Thu Aug 30, 2012 2:10 pm
by soitjes
With such simple table I'm pretty sure that you will quickly run into performance problems. I also use such a table where I store all the raw values I receive from devices, but every minute, hour and day I calculate the average, total, min and max values for that respectively minute, hour or day. Those values go in separate tables (called cache tables). I use a feature from the database engine to do that in separate threads, so that the main table is always available (and so my application doesn't lock while running those queries).

Now let's say I would like to plot the temperature of all rooms during the last month : instead of going over all the records for all temp devices I simply read the pre-calculated value of each day of the month, and plot it. That is max. 31 records per device in a simple SELECT query, instead of a several hundreds in a complex SELECT with subquery and aggregate functions. Imagin that you do the same for a whole year...

Obviously, not only the database will become more complex, but also your application code. Because to plot the current month I have to read from the cache tables until yesterday, and for today I can only use hour and minute cache values and for the last minute I have to use the raw values. But like any decent algorithm : a lot of code does not mean slow code :) All my charts are almost instantly plotted.

Soitjes.