MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `nodes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB /* AUTO_INCREMENT=6 */ DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `nodes` (id) VALUES (1), (2)
CREATE TABLE `monitors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB /* AUTO_INCREMENT=1315 */ DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `monitors` (id) VALUES (1), (2)
CREATE TABLE `monitor_node` (
  `monitor_id` int unsigned NOT NULL,
  `node_id` int unsigned NOT NULL,
  `average_response_time` double(8,2) DEFAULT NULL,
  UNIQUE KEY `monitor_node_monitor_id_node_id_unique` (`monitor_id`,`node_id`),
  KEY `monitor_node_node_id_foreign` (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `monitor_node` (monitor_id, node_id, average_response_time) 
VALUES
(1,1,0),
(1,2,0),
(2,1,0),
(2,2,0)
CREATE TABLE `monitor_checks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `monitor_id` int unsigned NOT NULL,
  `node_id` int unsigned NOT NULL,
  `response_time` double(8,6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `monitor_checks_monitor_id_node_id_response_time_index` (`monitor_id`,`node_id`,`response_time`),
  KEY `monitor_checks_node_id_foreign` (`node_id`)
) ENGINE=InnoDB /* AUTO_INCREMENT=3964257 */ DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `monitor_checks` (monitor_id, node_id, response_time)
VALUES
(1,1,0.5),
(1,2,0.3),
(1,1,0.1),
(1,2,0.2),
(2,1,0.8),
(2,2,0.9),
(2,1,0.3),
(2,2,0.4)
ALTER TABLE `monitor_checks`
ADD CONSTRAINT `monitor_checks_monitor_id_foreign` FOREIGN KEY (`monitor_id`) REFERENCES `monitors` (`id`),
ADD CONSTRAINT `monitor_checks_node_id_foreign` FOREIGN KEY (`node_id`) REFERENCES `nodes` (`id`);
ALTER TABLE `monitor_node`
ADD CONSTRAINT `monitor_node_monitor_id_foreign` FOREIGN KEY (`monitor_id`) REFERENCES `monitors` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `monitor_node_node_id_foreign` FOREIGN KEY (`node_id`) REFERENCES `nodes` (`id`) ON DELETE CASCADE;
select * from nodes;
select * from monitors;
select * from monitor_checks;
select * from monitor_node;


id


1

2




id


1

2




id
monitor_id
node_id
response_time


3
1
1
0.100000

1
1
1
0.500000

4
1
2
0.200000

2
1
2
0.300000

7
2
1
0.300000

5
2
1
0.800000

8
2
2
0.400000

6
2
2
0.900000




monitor_id
node_id
average_response_time


1
1
0.00

1
2
0.00

2
1
0.00

2
2
0.00


SELECT monitor_id, node_id, AVG(response_time) average_response_time
FROM monitor_checks
GROUP BY monitor_id, node_id;


monitor_id
node_id
average_response_time


1
1
0.3000000000

1
2
0.2500000000

2
1
0.5500000000

2
2
0.6500000000


UPDATE monitor_node
NATURAL JOIN ( SELECT monitor_id, node_id, AVG(response_time) avg_response_time
               FROM monitor_checks
               GROUP BY monitor_id, node_id ) check_result
SET average_response_time = avg_response_time;
SELECT * FROM monitor_node;


monitor_id
node_id
average_response_time


1
1
0.30

1
2
0.25

2
1
0.55

2
2
0.65


EXPLAIN 
SELECT monitor_id, node_id, AVG(response_time) average_response_time
FROM monitor_checks
GROUP BY monitor_id, node_id;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
monitor_checks
null
index
monitor_checks_monitor_id_node_id_response_time_index
monitor_checks_monitor_id_node_id_response_time_index
17
null
1
100.00
Using index


EXPLAIN
UPDATE monitor_node
NATURAL JOIN ( SELECT monitor_id, node_id, AVG(response_time) avg_response_time
               FROM monitor_checks
               GROUP BY monitor_id, node_id ) check_result
SET average_response_time = avg_response_time;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
UPDATE
monitor_node
null
ALL
monitor_node_monitor_id_node_id_unique,monitor_node_node_id_foreign
null
null
null
1
100.00
null

1
PRIMARY
<derived2>
null
ref
<auto_key0>
<auto_key0>
8
fiddle_EOUGJMVCUUXRNHQAXATU.monitor_node.monitor_id,fiddle_EOUGJMVCUUXRNHQAXATU.monitor_node.node_id
2
100.00
null

2
DERIVED
monitor_checks
null
index
monitor_checks_monitor_id_node_id_response_time_index
monitor_checks_monitor_id_node_id_response_time_index
17
null
1
100.00
Using index

id	monitor_id	node_id	response_time
3	1	1	0.100000
1	1	1	0.500000
4	1	2	0.200000
2	1	2	0.300000
7	2	1	0.300000
5	2	1	0.800000
8	2	2	0.400000
6	2	2	0.900000

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	monitor_node	null	ALL	monitor_node_monitor_id_node_id_unique,monitor_node_node_id_foreign	null	null	null	1	100.00	null
1	PRIMARY	<derived2>	null	ref	<auto_key0>	<auto_key0>	8	fiddle_EOUGJMVCUUXRNHQAXATU.monitor_node.monitor_id,fiddle_EOUGJMVCUUXRNHQAXATU.monitor_node.node_id	2	100.00	null
2	DERIVED	monitor_checks	null	index	monitor_checks_monitor_id_node_id_response_time_index	monitor_checks_monitor_id_node_id_response_time_index	17	null	1	100.00	Using index