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.