clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2043189 fiddles created (16644 in the last week).

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;
 hidden batch(es)


INSERT INTO `nodes` (id) VALUES (1), (2)
 hidden batch(es)


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;
 hidden batch(es)


INSERT INTO `monitors` (id) VALUES (1), (2)
 hidden batch(es)


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;
 hidden batch(es)


INSERT INTO `monitor_node` (monitor_id, node_id, average_response_time) VALUES (1,1,0), (1,2,0), (2,1,0), (2,2,0)
 hidden batch(es)


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;
 hidden batch(es)


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)
 hidden batch(es)


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`);
 hidden batch(es)


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;
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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 index monitor_checks_monitor_id_node_id_response_time_index monitor_checks_monitor_id_node_id_response_time_index 17 1 100.00 Using index
 hidden batch(es)


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 ALL monitor_node_monitor_id_node_id_unique,monitor_node_node_id_foreign 1 100.00
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 8 fiddle_EOUGJMVCUUXRNHQAXATU.monitor_node.monitor_id,fiddle_EOUGJMVCUUXRNHQAXATU.monitor_node.node_id 2 100.00
2 DERIVED monitor_checks index monitor_checks_monitor_id_node_id_response_time_index monitor_checks_monitor_id_node_id_response_time_index 17 1 100.00 Using index
 hidden batch(es)