storing samples of data in mysql

[Ehud Shabtai]

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.

[Aggro]

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.

[Aggro]

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.

[Aggro]

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.

[es22]

With this approach I have to insert a new record every second even though it
will be the same as the last record (The data in the devices does not change
every second). Isn't there a way to insert new records only when something
changes and still be able to query the information I need?

Ehud.

"Aggro" <sp**********@yahoo.com> wrote in message
news:hT**************@read3.inet.fi...

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.

[es22]

With this approach I have to insert a new record every second even though it
will be the same as the last record (The data in the devices does not change
every second). Isn't there a way to insert new records only when something
changes and still be able to query the information I need?

Ehud.

"Aggro" <sp**********@yahoo.com> wrote in message
news:hT**************@read3.inet.fi...

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.

[es22]

With this approach I have to insert a new record every second even though it
will be the same as the last record (The data in the devices does not change
every second). Isn't there a way to insert new records only when something
changes and still be able to query the information I need?

Ehud.

"Aggro" <sp**********@yahoo.com> wrote in message
news:hT**************@read3.inet.fi...

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.

[Aggro]

es22 wrote:

With this approach I have to insert a new record every second even though it
will be the same as the last record (The data in the devices does not change
every second). Isn't there a way to insert new records only when something
changes and still be able to query the information I need?

I don't understand why would you need to insert it?

Simple example:

create table device_value(
device int unsigned,
value int,
insert_time datetime );

insert into device_value values(1,5,'2004-04-20 20:00:00');
insert into device_value values(2,3,'2004-04-20 20:05:00');
insert into device_value values(1,3,'2004-04-20 20:10:00');

mysql> select * from device_value;
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 2 | 3 | 2004-04-20 20:05:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
+--------+-------+---------------------+
Let's imagine that you have new value(7) for device 1. And you want to
know wheter you should insert it or not.

select max(insert_time) from device_value where device=1 and value=7;

If this returns null, then you should insert the row. If it returns you
a datetime value, it means that you already have that value inserted for
that device and you shouldn't insert it. i.e.

mysql> select max(insert_time) from device_value where device=1 and value=3;
+---------------------+
| max(insert_time) |
+---------------------+
| 2004-04-20 20:10:00 |
+---------------------+

mysql> select max(insert_time) from device_value where device=1 and value=7;
+------------------+
| max(insert_time) |
+------------------+
| NULL |
+------------------+

[Aggro]

es22 wrote:

With this approach I have to insert a new record every second even though it
will be the same as the last record (The data in the devices does not change
every second). Isn't there a way to insert new records only when something
changes and still be able to query the information I need?

I don't understand why would you need to insert it?

Simple example:

create table device_value(
device int unsigned,
value int,
insert_time datetime );

insert into device_value values(1,5,'2004-04-20 20:00:00');
insert into device_value values(2,3,'2004-04-20 20:05:00');
insert into device_value values(1,3,'2004-04-20 20:10:00');

mysql> select * from device_value;
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 2 | 3 | 2004-04-20 20:05:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
+--------+-------+---------------------+
Let's imagine that you have new value(7) for device 1. And you want to
know wheter you should insert it or not.

select max(insert_time) from device_value where device=1 and value=7;

If this returns null, then you should insert the row. If it returns you
a datetime value, it means that you already have that value inserted for
that device and you shouldn't insert it. i.e.

mysql> select max(insert_time) from device_value where device=1 and value=3;
+---------------------+
| max(insert_time) |
+---------------------+
| 2004-04-20 20:10:00 |
+---------------------+

mysql> select max(insert_time) from device_value where device=1 and value=7;
+------------------+
| max(insert_time) |
+------------------+
| NULL |
+------------------+

[Aggro]

es22 wrote:

With this approach I have to insert a new record every second even though it
will be the same as the last record (The data in the devices does not change
every second). Isn't there a way to insert new records only when something
changes and still be able to query the information I need?

I don't understand why would you need to insert it?

Simple example:

create table device_value(
device int unsigned,
value int,
insert_time datetime );

insert into device_value values(1,5,'2004-04-20 20:00:00');
insert into device_value values(2,3,'2004-04-20 20:05:00');
insert into device_value values(1,3,'2004-04-20 20:10:00');

mysql> select * from device_value;
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 2 | 3 | 2004-04-20 20:05:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
+--------+-------+---------------------+
Let's imagine that you have new value(7) for device 1. And you want to
know wheter you should insert it or not.

select max(insert_time) from device_value where device=1 and value=7;

If this returns null, then you should insert the row. If it returns you
a datetime value, it means that you already have that value inserted for
that device and you shouldn't insert it. i.e.

mysql> select max(insert_time) from device_value where device=1 and value=3;
+---------------------+
| max(insert_time) |
+---------------------+
| 2004-04-20 20:10:00 |
+---------------------+

mysql> select max(insert_time) from device_value where device=1 and value=7;
+------------------+
| max(insert_time) |
+------------------+
| NULL |
+------------------+

[es22]

First, thanks for your help.

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater
than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59

If I had samples each second this could have been easy to get however, it is
a waste of space t have the same record over and over. Is there a way to get
the result I want with the table you suggest?

2. What if the two devices have different data types (one integer and one
float), would you suggest to use two tables?
If I have 500 devices, should I still use 1 table for all devices or maybe
using 500 different tables is more efficient (as each table will be very
small)?

Thanks,

Ehud.

"Aggro" <sp**********@yahoo.com> wrote in message
news:%O**************@read3.inet.fi...

es22 wrote:

With this approach I have to insert a new record every second even though it will be the same as the last record (The data in the devices does not change every second). Isn't there a way to insert new records only when something changes and still be able to query the information I need?
I don't understand why would you need to insert it?

Simple example:

create table device_value(
device int unsigned,
value int,
insert_time datetime );

insert into device_value values(1,5,'2004-04-20 20:00:00');
insert into device_value values(2,3,'2004-04-20 20:05:00');
insert into device_value values(1,3,'2004-04-20 20:10:00');

mysql> select * from device_value;
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 2 | 3 | 2004-04-20 20:05:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
+--------+-------+---------------------+
Let's imagine that you have new value(7) for device 1. And you want to
know wheter you should insert it or not.

select max(insert_time) from device_value where device=1 and value=7;

If this returns null, then you should insert the row. If it returns you
a datetime value, it means that you already have that value inserted for
that device and you shouldn't insert it. i.e.

mysql> select max(insert_time) from device_value where device=1 and

value=3; +---------------------+
| max(insert_time) |
+---------------------+
| 2004-04-20 20:10:00 |
+---------------------+

mysql> select max(insert_time) from device_value where device=1 and value=7; +------------------+
| max(insert_time) |
+------------------+
| NULL |
+------------------+

[es22]

First, thanks for your help.

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater
than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59

If I had samples each second this could have been easy to get however, it is
a waste of space t have the same record over and over. Is there a way to get
the result I want with the table you suggest?

2. What if the two devices have different data types (one integer and one
float), would you suggest to use two tables?
If I have 500 devices, should I still use 1 table for all devices or maybe
using 500 different tables is more efficient (as each table will be very
small)?

Thanks,

Ehud.

"Aggro" <sp**********@yahoo.com> wrote in message
news:%O**************@read3.inet.fi...

es22 wrote:

With this approach I have to insert a new record every second even though it will be the same as the last record (The data in the devices does not change every second). Isn't there a way to insert new records only when something changes and still be able to query the information I need?
I don't understand why would you need to insert it?

Simple example:

create table device_value(
device int unsigned,
value int,
insert_time datetime );

insert into device_value values(1,5,'2004-04-20 20:00:00');
insert into device_value values(2,3,'2004-04-20 20:05:00');
insert into device_value values(1,3,'2004-04-20 20:10:00');

mysql> select * from device_value;
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 2 | 3 | 2004-04-20 20:05:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
+--------+-------+---------------------+
Let's imagine that you have new value(7) for device 1. And you want to
know wheter you should insert it or not.

select max(insert_time) from device_value where device=1 and value=7;

If this returns null, then you should insert the row. If it returns you
a datetime value, it means that you already have that value inserted for
that device and you shouldn't insert it. i.e.

mysql> select max(insert_time) from device_value where device=1 and

value=3; +---------------------+
| max(insert_time) |
+---------------------+
| 2004-04-20 20:10:00 |
+---------------------+

mysql> select max(insert_time) from device_value where device=1 and value=7; +------------------+
| max(insert_time) |
+------------------+
| NULL |
+------------------+

[es22]

First, thanks for your help.

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater
than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59

If I had samples each second this could have been easy to get however, it is
a waste of space t have the same record over and over. Is there a way to get
the result I want with the table you suggest?

2. What if the two devices have different data types (one integer and one
float), would you suggest to use two tables?
If I have 500 devices, should I still use 1 table for all devices or maybe
using 500 different tables is more efficient (as each table will be very
small)?

Thanks,

Ehud.

"Aggro" <sp**********@yahoo.com> wrote in message
news:%O**************@read3.inet.fi...

es22 wrote:

With this approach I have to insert a new record every second even though it will be the same as the last record (The data in the devices does not change every second). Isn't there a way to insert new records only when something changes and still be able to query the information I need?
I don't understand why would you need to insert it?

Simple example:

create table device_value(
device int unsigned,
value int,
insert_time datetime );

insert into device_value values(1,5,'2004-04-20 20:00:00');
insert into device_value values(2,3,'2004-04-20 20:05:00');
insert into device_value values(1,3,'2004-04-20 20:10:00');

mysql> select * from device_value;
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 2 | 3 | 2004-04-20 20:05:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
+--------+-------+---------------------+
Let's imagine that you have new value(7) for device 1. And you want to
know wheter you should insert it or not.

select max(insert_time) from device_value where device=1 and value=7;

If this returns null, then you should insert the row. If it returns you
a datetime value, it means that you already have that value inserted for
that device and you shouldn't insert it. i.e.

mysql> select max(insert_time) from device_value where device=1 and

value=3; +---------------------+
| max(insert_time) |
+---------------------+
| 2004-04-20 20:10:00 |
+---------------------+

mysql> select max(insert_time) from device_value where device=1 and value=7; +------------------+
| max(insert_time) |
+------------------+
| NULL |
+------------------+

[Aggro]

es22 wrote:

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater
than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59
What if the data is smaller in the middle of two edges? In that case you
can either ignore the middle part, or you can have two time limits. How
should it be in that case?

We could for example have test data like this:

+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 12 | 2004-04-20 20:20:00 |
| 1 | 2 | 2004-04-20 20:23:00 |
+--------+-------+---------------------+

2. What if the two devices have different data types (one integer and one
float), would you suggest to use two tables?
You could have two columns, one for each data type. And either use null
value in the other column to mark which is used, or use third column to
identify which column is used if that suits better for your needs.
If I have 500 devices, should I still use 1 table for all devices or maybe
using 500 different tables is more efficient (as each table will be very
small)?

If you use indexes, it doesn't matter.

[Aggro]

es22 wrote:

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater
than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59
What if the data is smaller in the middle of two edges? In that case you
can either ignore the middle part, or you can have two time limits. How
should it be in that case?

We could for example have test data like this:

+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 12 | 2004-04-20 20:20:00 |
| 1 | 2 | 2004-04-20 20:23:00 |
+--------+-------+---------------------+

2. What if the two devices have different data types (one integer and one
float), would you suggest to use two tables?
You could have two columns, one for each data type. And either use null
value in the other column to mark which is used, or use third column to
identify which column is used if that suits better for your needs.
If I have 500 devices, should I still use 1 table for all devices or maybe
using 500 different tables is more efficient (as each table will be very
small)?

If you use indexes, it doesn't matter.

[Aggro]

es22 wrote:

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater
than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59
What if the data is smaller in the middle of two edges? In that case you
can either ignore the middle part, or you can have two time limits. How
should it be in that case?

We could for example have test data like this:

+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 12 | 2004-04-20 20:20:00 |
| 1 | 2 | 2004-04-20 20:23:00 |
+--------+-------+---------------------+

2. What if the two devices have different data types (one integer and one
float), would you suggest to use two tables?
You could have two columns, one for each data type. And either use null
value in the other column to mark which is used, or use third column to
identify which column is used if that suits better for your needs.
If I have 500 devices, should I still use 1 table for all devices or maybe
using 500 different tables is more efficient (as each table will be very
small)?

If you use indexes, it doesn't matter.

[es22]

"Aggro" <sp**********@yahoo.com> wrote in message
news:w_**************@read3.inet.fi...

es22 wrote:

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59

What if the data is smaller in the middle of two edges? In that case you
can either ignore the middle part, or you can have two time limits. How
should it be in that case?

We could for example have test data like this:

+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 12 | 2004-04-20 20:20:00 |
| 1 | 2 | 2004-04-20 20:23:00 |
+--------+-------+---------------------+

I can't ignore any middle parts.
This table actully represents:
device 1 has:
value 5 from 20:00:00 to 20:09:59.
value 3 from 20:10:00 to 20:13:59.
value 6 from 20:14:00 to 20:15:59.
and so on.

So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59

One way to solve it may be with adding a duplicated record which indicates
the last time a value existed:
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 5 | 2004-04-20 20:09:59 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 3 | 2004-04-20 20:13:59 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 6 | 2004-04-20 20:15:59 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 2 | 2004-04-20 20:19:59 |
+--------+-------+---------------------+

Here, the duplicated data is minimal.

Is there any other way?

2. What if the two devices have different data types (one integer and one float), would you suggest to use two tables?

You could have two columns, one for each data type. And either use null
value in the other column to mark which is used, or use third column to
identify which column is used if that suits better for your needs.

If I have 500 devices, should I still use 1 table for all devices or maybe using 500 different tables is more efficient (as each table will be very
small)?

If you use indexes, it doesn't matter.

So using 500 tables has the same performance of 1 big table with 500
different devices?

[es22]

"Aggro" <sp**********@yahoo.com> wrote in message
news:w_**************@read3.inet.fi...

es22 wrote:

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59

What if the data is smaller in the middle of two edges? In that case you
can either ignore the middle part, or you can have two time limits. How
should it be in that case?

We could for example have test data like this:

+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 12 | 2004-04-20 20:20:00 |
| 1 | 2 | 2004-04-20 20:23:00 |
+--------+-------+---------------------+

I can't ignore any middle parts.
This table actully represents:
device 1 has:
value 5 from 20:00:00 to 20:09:59.
value 3 from 20:10:00 to 20:13:59.
value 6 from 20:14:00 to 20:15:59.
and so on.

So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59

One way to solve it may be with adding a duplicated record which indicates
the last time a value existed:
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 5 | 2004-04-20 20:09:59 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 3 | 2004-04-20 20:13:59 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 6 | 2004-04-20 20:15:59 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 2 | 2004-04-20 20:19:59 |
+--------+-------+---------------------+

Here, the duplicated data is minimal.

Is there any other way?

2. What if the two devices have different data types (one integer and one float), would you suggest to use two tables?

You could have two columns, one for each data type. And either use null
value in the other column to mark which is used, or use third column to
identify which column is used if that suits better for your needs.

If I have 500 devices, should I still use 1 table for all devices or maybe using 500 different tables is more efficient (as each table will be very
small)?

If you use indexes, it doesn't matter.

So using 500 tables has the same performance of 1 big table with 500
different devices?

[es22]

"Aggro" <sp**********@yahoo.com> wrote in message
news:w_**************@read3.inet.fi...

es22 wrote:

I still have a couple of questions:
1. How can I query something like:'when - the data from device 1 is greater than 4 and the data from device 2 is greater than 2'.
In your example I expect the query to return something like:
from 2004-04-20 20:05:00 to 2004-04-20 20:09:59

What if the data is smaller in the middle of two edges? In that case you
can either ignore the middle part, or you can have two time limits. How
should it be in that case?

We could for example have test data like this:

+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 12 | 2004-04-20 20:20:00 |
| 1 | 2 | 2004-04-20 20:23:00 |
+--------+-------+---------------------+

I can't ignore any middle parts.
This table actully represents:
device 1 has:
value 5 from 20:00:00 to 20:09:59.
value 3 from 20:10:00 to 20:13:59.
value 6 from 20:14:00 to 20:15:59.
and so on.

So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59

One way to solve it may be with adding a duplicated record which indicates
the last time a value existed:
+--------+-------+---------------------+
| device | value | insert_time |
+--------+-------+---------------------+
| 1 | 5 | 2004-04-20 20:00:00 |
| 1 | 5 | 2004-04-20 20:09:59 |
| 1 | 3 | 2004-04-20 20:10:00 |
| 1 | 3 | 2004-04-20 20:13:59 |
| 1 | 6 | 2004-04-20 20:14:00 |
| 1 | 6 | 2004-04-20 20:15:59 |
| 1 | 2 | 2004-04-20 20:16:00 |
| 1 | 2 | 2004-04-20 20:19:59 |
+--------+-------+---------------------+

Here, the duplicated data is minimal.

Is there any other way?

2. What if the two devices have different data types (one integer and one float), would you suggest to use two tables?

You could have two columns, one for each data type. And either use null
value in the other column to mark which is used, or use third column to
identify which column is used if that suits better for your needs.

If I have 500 devices, should I still use 1 table for all devices or maybe using 500 different tables is more efficient (as each table will be very
small)?

If you use indexes, it doesn't matter.

So using 500 tables has the same performance of 1 big table with 500
different devices?

[Aggro]

es22 wrote:

I can't ignore any middle parts. So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59
You say that data amount for each device is quite small. One solution
would then be that you just select all data for that device and parse it
in your application.

If that is not possible, then there might be somekind of solution by
using joins or subselects (which can be used starting from MySQL version= 4.1). Anyway making such a query can be pretty hard. At least it

would be that for me, and I don't think I can help with that.

[Aggro]

es22 wrote:

I can't ignore any middle parts. So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59
You say that data amount for each device is quite small. One solution
would then be that you just select all data for that device and parse it
in your application.

If that is not possible, then there might be somekind of solution by
using joins or subselects (which can be used starting from MySQL version= 4.1). Anyway making such a query can be pretty hard. At least it

would be that for me, and I don't think I can help with that.

[Aggro]

es22 wrote:

I can't ignore any middle parts. So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59
You say that data amount for each device is quite small. One solution
would then be that you just select all data for that device and parse it
in your application.

If that is not possible, then there might be somekind of solution by
using joins or subselects (which can be used starting from MySQL version= 4.1). Anyway making such a query can be pretty hard. At least it

would be that for me, and I don't think I can help with that.

[es22]

Thanks Aggro,

Can anyone else advise here?

"Aggro" <sp**********@yahoo.com> wrote in message
news:9E**************@read3.inet.fi...

es22 wrote:

I can't ignore any middle parts.

So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59

You say that data amount for each device is quite small. One solution
would then be that you just select all data for that device and parse it
in your application.

If that is not possible, then there might be somekind of solution by
using joins or subselects (which can be used starting from MySQL version

>= 4.1). Anyway making such a query can be pretty hard. At least it

would be that for me, and I don't think I can help with that.

[es22]

Thanks Aggro,

Can anyone else advise here?

"Aggro" <sp**********@yahoo.com> wrote in message
news:9E**************@read3.inet.fi...

es22 wrote:

I can't ignore any middle parts.

So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59

You say that data amount for each device is quite small. One solution
would then be that you just select all data for that device and parse it
in your application.

If that is not possible, then there might be somekind of solution by
using joins or subselects (which can be used starting from MySQL version

>= 4.1). Anyway making such a query can be pretty hard. At least it

would be that for me, and I don't think I can help with that.

[es22]

Thanks Aggro,

Can anyone else advise here?

"Aggro" <sp**********@yahoo.com> wrote in message
news:9E**************@read3.inet.fi...

es22 wrote:

I can't ignore any middle parts.

So I need to be able to answer a query like: 'when device 1 has values
greater than 4'
and the result should be:
5 - from 20:00:00 to 20:09:59
6 - from 20:14:00 to 20:15:59

You say that data amount for each device is quite small. One solution
would then be that you just select all data for that device and parse it
in your application.

If that is not possible, then there might be somekind of solution by
using joins or subselects (which can be used starting from MySQL version

>= 4.1). Anyway making such a query can be pretty hard. At least it

would be that for me, and I don't think I can help with that.