mysql数据文件 样本,在mysql中存储数据样本

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.

解决方案Ehud Shabtai wrote:

Please advise,

Use one table and be sure to use indexes.

And when you do queries don''t compare time of two different devices,

just use a time limit to get all values from wanted timeperiod.

Then in your program use this data and fill in the missing parts.

This is the "correct way" to do this IMHO. Because

1) Table structure is simple

2) Queries are simple and fast

3) Data which queries return is small ( This is usually a good thing.

Especially if you are using a network database. )

There might be some problems with this. You might need to be able to

retrieve the value before time limit(extra query something like ''select

* from table where time < timelimit limit 1''), unless you want to get

all data for devices.

Ehud Shabtai wrote:

Please advise,

Use one table and be sure to use indexes.

And when you do queries don''t compare time of two different devices,

just use a time limit to get all values from wanted timeperiod.

Then in your program use this data and fill in the missing parts.

This is the "correct way" to do this IMHO. Because

1) Table structure is simple

2) Queries are simple and fast

3) Data which queries return is small ( This is usually a good thing.

Especially if you are using a network database. )

There might be some problems with this. You might need to be able to

retrieve the value before time limit(extra query something like ''select

* from table where time < timelimit limit 1''), unless you want to get

all data for devices.

Ehud Shabtai wrote:

Please advise,

Use one table and be sure to use indexes.

And when you do queries don''t compare time of two different devices,

just use a time limit to get all values from wanted timeperiod.

Then in your program use this data and fill in the missing parts.

This is the "correct way" to do this IMHO. Because

1) Table structure is simple

2) Queries are simple and fast

3) Data which queries return is small ( This is usually a good thing.

Especially if you are using a network database. )

There might be some problems with this. You might need to be able to

retrieve the value before time limit(extra query something like ''select

* from table where time < timelimit limit 1''), unless you want to get

all data for devices.