Best data type to save my data

[Rubble]

I am blinging out my greenhouse with various sensors - mainly to give me something to do but the results are interesting.

I intend on having ( with probable range ):
temp -10.00 to 30.00
Humidity 30.02 to 100.00
pressure 1009.43
Soil sensor 20 to 600
Soil sensor 20 to 600
Light 20 to 600
Battery 20 to 600

The data from my microprocessor will be in this format:
15.01,60.30,1009.43,120,130,345,400

My question is would be better and/or it take up less database room as one long string or break it up into the individual number?

I will be saving every 10min so 144 records a day - 52,560/year

Saving as one long string means I will have to use more processing when selecting the data from the string with php.
Currently I am saving temp, humidity and pressure into a monthly csv file which works and I could stay with that method.

[r937]

use separate columns with the appropriate datatypes

50k rows per year is trivially small

[Rubble]

Thank you r937; 50K sounded a lot to me as I have never saved that amount of data before!

[Mittineague]

@Rubble

50K sounded a lot to me

I’ve worked with some tables with ~300K rows and run simple queries without much noticeable problems. Running more complex JOIN queries against more than one large-ish sized tables can take a few minutes.

Where I have noticed more a problem is with putting the results into PHP causing memory and script execution time issues.

That is, the more likely “bottleneck” isn’t the database, but PHP.

[Rubble]

@Mittineague

That is, the more likely “bottleneck” isn’t the database, but PHP.

I am not doing anything complicated; basically just selecting and displaying the data but was also thinking about that. At the moment I am using csv files ( temperature, humidity and pressure ) and starting a new file every month; so I am dealing with a lot smaller amount of data. I actually have a “current” csv file as well that is the last 30 days so it covers more than the one calendar month.
I suppose if I had problems I could have a new table every year.

[SamA74]

I don’t know any hard facts about how it affects performance or data size, but my gut feeling is you will make life so much easier using separate columns which will probably help script performance and any space saving (if any) from joining the data would probably be neglagable.

@Rubble

I actually have a “current” csv file as well that is the last 30 days so it covers more than the one calendar month.
I suppose if I had problems I could have a new table every year.

If for the most part you only need data for the last day, month or year, rather than all-time, you can query just the recent records. I should think the timestamps will be unique and could be used as indexes.

[John_Betong]

@Rubble

Thank you r937; 50K sounded a lot to me as I have never saved that amount of data before!

I am in the process of analysing similar data and for the past couple of months downloaded from numerous Sensor CSV files and now have the following:

Date start: 2018-10-03 14:12:00
Yesterday: 2019-02-16 03:37:20
Rows: 361,972

Zipped file size: 916.4 kB (916,370 bytes)

CREATE TABLE tblValues (
id int(6) NOT NULL,
dev_id int(8) NOT NULL DEFAULT ‘0’,
battery int(8) NOT NULL,
humidity int(4) NOT NULL DEFAULT ‘55’,
temperature decimal(8,2) NOT NULL,
time varchar(42) NOT NULL,
tstamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
raw varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[Rubble]

I was going to use some similar types @John_Betong but probably a couple more decimal ones.

I intend on saving “forever” @SamA74 but I do tend to split it into months but that was mainly for the csv files. I was going to add some more statistics like hottest day this year etc. I was thinking about having a separate table for this and just check the new value against the current value stored and overwrite if better. This should be more efficient than searching through every record for the hottest temperature. As you say I think small chunks are more manageable.