Howdy MySQL Subscribers.
I'm hoping some MySQL users more experienced than myself can shed light on
a work-related question that has recently arisen. We have a database with
24 meter tables similar to meter1 below.
mysql> explain meter1;
+------------------------------------+--------------------------+-------+-------+-------------------------------+--------+
| Field | Type | Null | Key |
Default | Extra |
+------------------------------------+--------------------------+-------+-------+-------------------------------+--------+
| date_time | datetime | |
| 0000-00-00 00:00:00 | |
| count_per_minute | int(11) | | |
0 | |
| dose_equiv_per_minute | double unsigned | YES | | NULL |
|
| counts | varchar(255) | YES
| | NULL | |
+------------------------------------+--------------------------+--------+------+--------------------------------+-------+
The tables are populated with data from neutron monitoring stations; data
are collected from the monitors and inserted into their respective tables
once per minute. Currently there are some 45,000+ records in each table,
though once in production mode we expect significantly higher record
counts ( the schedule of data backups and table truncations has yet to be
determined ). The data looks like this:
mysql> select date_time, count_per_minute, dose_equiv_per_minute from
meter1 order by date_time desc limit 40000, 10;
+-----------------------------+---------------------------+------------------------------------+
| date_time | count_per_minute | dose_equiv_per_minute
|
+-----------------------------+---------------------------+------------------------------------+
| 2003-11-25 19:51:01 | 2310 | 0.483598 |
| 2003-11-25 19:50:01 | 2316 | 0.484855 |
| 2003-11-25 19:48:01 | 772 | 0.161618 |
| 2003-11-25 19:47:01 | 3846 | 0.80516 |
| 2003-11-25 19:46:01 | 8 | 0.001675 |
| 2003-11-25 19:44:01 | 2313 | 0.484227 |
| 2003-11-25 19:43:00 | 2313 | 0.484227 |
| 2003-11-25 19:41:00 | 1032 | 0.216049 |
| 2003-11-25 19:40:00 | 3587 | 0.750938 |
| 2003-11-25 19:39:00 | 7 | 0.001465 |
+-----------------------------+---------------------------+------------------------------------+
We have a web GUI written in PHP that calls a C program which queries the
database, analyzes the data, and creates a PDF report. One of many
analytical tasks of this program is to find the maximum sum of the field,
`dose_equiv_per_minute`, from any 60 consecutive records. In other words,
we need to sum the values from `dose_equiv_per_minute` for records 1
through 60, 2 through 61, 3 through 62, etc... then determine which is
the max sum. Currently this is being handled on the client-side through
the C program, which selects all of the (many thousands of ) records, then
does the math and loops through them, group by group. As you can imagine,
if one were creating reports from all 24 monitors, the number-crunching
can be quite time consuming (upwards of 5 minutes at present for just over
6 weeks worth of data). So the question is, can we save some time by
asking the MySQL server to crunch the numbers, and if so, what would the
query be to accomplish this task?
Any and all suggestions greatly appreciated.
TIA ,
dave
