Re: Multiple complex queries
Hi Rick
I had assumed that the TIMESTAMP type automatically inserted current server values. Am I wrong? I had not used it as the data collection boxes operate on a battery backed supply which will store data for up to a week in the event of power failures or network problems. (Some of the data collection points are difficult to get at. One is on a pole which can only be accessed at low tide!). For this reason data is timestamped on collection, not insertion into the database.
I think the 60x speed up was coincidental as the system is configured such that irrespective of the time period under examination, 360 rows of data are returned. The real speed up came from the use of the stored procedure to put the data in a summary table. The procedure is below
DELIMITER $$
DROP PROCEDURE IF EXISTS `getdata_a0000001` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getdata_a0000001`(IN p_starttime DATETIME, IN p_timespan INT)
BEGIN
DECLARE v_start DATETIME;
DECLARE v_end DATETIME;
DECLARE v_blockend DATETIME;
DECLARE v_RT DATETIME;
DECLARE v_S1 DOUBLE;
DECLARE v_S2 DOUBLE;
DECLARE v_S3 DOUBLE;
DECLARE v_S4 DOUBLE;
DECLARE v_S5 DOUBLE;
DECLARE v_P DOUBLE;
DECLARE v_H DOUBLE;
DECLARE v_R DOUBLE;
DECLARE v_L DOUBLE;
DECLARE v_WS DOUBLE;
DECLARE v_WD DOUBLE;
DECLARE v_GS DOUBLE;
DECLARE v_GD DOUBLE;
TRUNCATE TABLE weatherdata.temp_a0000001;
SET v_start = p_starttime;
SET v_end = DATE_ADD(v_start, INTERVAL p_timespan SECOND);
SET v_blockend = DATE_ADD(v_start, INTERVAL p_timespan / 360 SECOND);
WHILE (v_start < v_end) DO
SELECT MAX(ReadTime), AVG(Temp1), AVG(Temp2), AVG(Temp3), AVG(Temp4),
AVG(Temp5), AVG(Pressure), AVG(Humidity), SUM(Rainfall), AVG(Light),
AVG(WindSpeed), AVG(WindDir), MAX(GustSpeed), AVG(GustDir) INTO v_RT, v_S1,
v_S2, v_S3, v_S4, v_S5, v_P, v_H, v_R, v_L, v_WS, v_WD, v_GS, v_GD FROM
weatherdata.sitedataa0000001 WHERE ReadTime BETWEEN v_start AND v_blockend;
INSERT INTO temp_a0000001 (ReadTime, S1, S2, S3, S4, S5, P, H, R, L, WS, WD,
GS, GD) VALUES(v_blockend, v_S1, v_S2, v_S3, v_S4, v_S5, v_P, v_H, v_R,
v_L, v_WS, v_WD, v_GS, v_GD);
SET v_start = v_blockend;
SET v_blockend = DATE_ADD(v_start, INTERVAL p_timespan / 360 SECOND);
END WHILE;
END $$
DELIMITER ;
I tried doing an INSERT INTO SELECT but ran into problems with winges about variables not existing so I put this to one side for the moment.
The above procedure produced the table and returned its content in 1.9 secs, extracting averages from 1440 rows of data in a base table of >1952000 rows, indexed on the ReadTime field.
Many thanks for you help and interest