Take the 2-minute tour ×

I have a database that stores readings from numerous gauges. Depending on the gauge, there may be data for that gauge every 1-5 minutes with multiple readings (i.e.- weather station and it stores 5 different readings).

I currently store the data by date. I group everything for a particular gauge for a single date together and store is as xml in a text column.

Would it be more efficient to store this data as individual rows?

I'm trying to decide which is the smartest solution long term (i.e.- fewer rows with more data per row, or many more rows, each being smaller).

It is a read heavy environment.


By 'efficient' I am most concerned about speed and resource usage (if I pull back a year's worth of data, which would be least processor/memory intensive).

I almost exclusively use this data to graph for clients. The number of readings per timestamp is variable, from 1 to 6 readings.

The database is currently an INNODB.


I'm looking more towards keeping the data in MySQL for ease of access/updating.

The answer I was looking for more was whether it would be smarter to save each individual timestamp (which may have multiple readings) in an individual row or whether to group multiple timestamps (i.e.- up to a day's worth) into a single row.


share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

It depends on exactly what you're trying to do with the data -- if it's only being used for graphing, and you don't need high resolution the further out it goes (eg, you're not trying to plot a graph with full temporal resolution for a day from 1 year ago), you might actually want to look at RRDTool rather than a relational database.

If you're going to ever need to do analysis of the values (how often does a place get above 80°F?), you'll want to store discrete values, not an XML structure; but you could also use flat file stuctures that are meant for dealing with this type of data (eg, CDF, NetCDF ... maybe even HDF )

update :

I'd store each time as a separate record, as it makes it easier to adjust the granularity when graphing. For instance, to extract the high/low/mean for each hour:

SELECT   min(date_obs),min(temp),max(temp),avg(temp)
FROM     observations
WHERE    date_obs between ...
GROUP BY floor(unix_timestamp(date_obs)/3600)

Also, it allows you to change the cadence for the measurements without needing to change the table structure.

share|improve this answer
Those data analysis options look nice, however I'm in a PHP shop and am reluctant to have to branch to other languages just for graphing (none of the tools have a PHP API). I think that would introduce more room for bugs. –  Patrick Jan 28 '11 at 15:41
It's not as pretty, but there are ways to work with RRD from PHP. –  Joe Jan 28 '11 at 16:00
I believe I will make the changes as you suggested and may, in the future, break out the data into different tables for each year to keep the tables from getting absolutely massive. Then I have to deal with getting symfony to understand that... other days - other problems –  Patrick Jan 28 '11 at 17:52
@Patrick : you might be able to use partitioning to avoid having to break them apart yourself, so long as you don't fall into any of the limitatons –  Joe Jan 28 '11 at 18:22
partitioning looks very interesting, I'm going to have to research how well I can implement this in symfony –  Patrick Jan 31 '11 at 1:54
add comment

If I understand correctly you currently have a table with fields 3 fields datetime, gauge, and readings, where readings is an XML string. I would have thought that you would be better to split up the readings as extra columns. Firstly, the string implementation probably takes more physical space. Secondly, it'll be relatively hard to do any kind of grouping, aggregation, filtering etc.

I guess it depends on what you mean my 'efficient'. It probably also depends on what and how you are reading the data, and where that data has to go. There may be less network traffic if it is stored as individual fields. Also depends on what the client reading the data needs to do with it.

share|improve this answer
I'll clarify the question. The data is used just for displaying the readings in a graph and the number of readings per gauge varies, some have only one reading and others have 6 readings. –  Patrick Jan 27 '11 at 15:20
In light of your clarification, and assuming you need a relational DB, then I'd split the data into fields. Speed shouldn't be a problem. As Joe pointed out, there may be other storage options more suitable. –  Miles D Jan 27 '11 at 15:45
add comment

The reason that I particularly use XML fields is because I may associate one record with a "Readings" element in the XML, and the next record may have a "Telemetry" XML element (to invent something) ...

Actually, for mine I have different payloads of data but the headers are all the same, so it's just easier for me to stuff it in an XML because I want the payload after I filter by the header information that the row represents, and I may have varying amounts of information in that field. For instance, one of my XML fields is record edit history. That way I can have an ever expanding field for that one record, and it's neatly packaged up with the row it represents, and I don't have to have a separate audit table for that one set of values. It may not be the most efficient way of doing this, but it works for me and my needs.

share|improve this answer
add comment

Your Answer


