Hi,

I would like to store samples of data which are gathered from about
500 devices in mysql. Each device has its own data which changes over
time. The data may be integer or float.

The queries I'm interested in our something like: "show me when the
data from device X was greater than 70 and the data from device Y was
greater than 90".

The trivial way of doing so is to use one large table in which each
column represents a device plus a datetime column. In this table I can
gather each second a sample from each device and store in the table.
This way I can easily query the data I need.

However, as the rate of change of the data in each device is low
(about 1 every 20 minutes average), I would like to store each device
in a table of its own and only add a new record when the data changes.
That way I will have 500 tables but each will be small. The problem is
that I can't figure out how can I do the queries I need.
Doing something like:
select x,y from table_x,table_y where (x>70 && y>90 &&
table_x.time=table_y.time);

Won't work as I don't insert every second a record into every table.

I may have two tables such as:

X Y
time data time data
03:00:00 69 03:30:00 92
04:00:00 70 04:30:00 93
05:00:00 71 05:30:00 94

Is there a way to tell mysql that these are points of samples in time,
and so it will actually expand the tables for the queries, something
like:

X
time data
03:00:00 69
03:00:01 69
03:00:02 69
.
.
.
03:59:59 69
04:00:00 70


Please advise,

Thanks,

Ehud.