Re: Best way to store/access large amounts of data?

[Rick James]

You have put some good thought into your design. Sorry, but you need to start over on the schema...

* DATETIME should be in a single field, not chopped into separate fields. It is much easier to split a field than to put it back together and do the WHERE clause.

* Build separate tables for separate values. There is nothing similar between speed and temperature, so don't put them in the same field. If you were recording temp and speed at the same time, they could be two separate columns in one table. But, since they are effectively separate readings, put them in separate tables. Later, you can do JOINs to correlate things. (That will be messy no matter how you do it.)

* You have several one-column keys. Usually that is a sign of a novice who has not heard of "compound keys" and what they can be useful for. Please research.

* Build summary tables. You mentioned "hourly averages"; that is a good start. But that really needs to be a separate table. Summary tables should have COUNT(*) from the 'Fact' table, plus SUM(...). Then an average is calculated from the sum of the sums divided by the sum of the counts. This allows you to get mathematically correct averages over any time span, not just a single hour.

What do you mean by "large" database? Gigabytes? Terabytes? When you get to megabytes, simple things can improve performance. When you get to gigabytes, the performance tricks get more involved. Terabytes will need serious discussions.

I did not look at your entire posting; hopefully some of your problems will go away after revising the schema, and rethinking how to do averages.