Hi,
I have two tables, both containing measurement values with (unix_)timestamps. What is the best way to left join one of these with the other on the nearest timestamp value?
Currently I am doing something like this:
CREATE TABLE Session
(Session_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Session_Data INT UNSIGNED NOT NULL);
CREATE TABLE Meas_Data
(Time INT UNSIGNED NOT NULL,
Id DEC(15) UNSIGNED ZEROFILL NOT NULL,
Data DEC(15) UNSIGNED ZEROFILL NOT NULL,
Session_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (Time, Id),
FOREIGN KEY (Session_id) REFERENCES Session (Session_id)
ON DELETE NO ACTION ON UPDATE CASCADE);
CREATE TABLE GPS
(Session_id BIGINT UNSIGNED NOT NULL,
UTC_s INT UNSIGNED NOT NULL COMMENT ‘Timestamp’,
UTC_us DEC(3) UNSIGNED NOT NULL COMMENT ‘microseconds’,
Longitude DOUBLE NOT NULL,
Latitude DOUBLE NOT NULL,
HDOP DEC(2,1) UNSIGNED,
Altitude FLOAT,
Fix ENUM(‘Invalid’, ‘2D’, ‘3D’),
COG FLOAT,
Speed FLOAT,
Sat DEC(2) UNSIGNED,
PRIMARY KEY (Session_id, UTC_s),
FOREIGN KEY (Session_id) REFERENCES Transmission_Log (Session_id)
ON DELETE CASCADE ON UPDATE NO ACTION);
CREATE FUNCTION get_gps_distance(mt_ INT UNSIGNED, si_ BIGINT UNSIGNED)
RETURNS INT UNSIGNED
COMMENT ‘Returns the time difference to the nearest GPS fix’
BEGIN
DECLARE rv_ INT UNSIGNED;
SELECT MIN(ABS(mt_ - UTC_s)) INTO rv_ FROM GPS
WHERE TL_id = si_;
RETURN rv_;
END;
SELECT *
FROM Meas_Data NATURAL JOIN Session
LEFT JOIN GPS
ON Session.TL_id = GPS.TL_id
AND ABS(Backoffice_View.Capture_Time - GPS.UTC_s)
= get_gps_distance(Backoffice_View.Capture_Time,
Backoffice_View.TL_id);
When I added the left join, my tests on realistic data experienced a factor of ten slowdown. Is there a better way to do such joins?
Thx
ImRe