Created
December 16, 2013 12:31
-
-
Save adrianlzt/7986263 to your computer and use it in GitHub Desktop.
explain icinga query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> EXPLAIN SELECT DISTINCT i3.icon_image AS i3__0, i6.instance_name AS i6__1, i.statehistory_id AS i__2, i4.host_object_id AS i4__3, i5.name1 AS i5__4, i4.alias AS i4__5, i4.display_name AS i4__6, i3.service_object_id AS i3__7, i2.name2 AS i2__8, i3.display_name AS i3__9, i.state AS i__10, i.state_time AS i__11, i.current_check_attempt AS i__12, i.max_check_attempts AS i__13, i.current_check_attempt AS i__14, i.max_check_attempts AS i__15 FROM icinga_statehistory i INNER JOIN icinga_objects i2 ON i.object_id = i2.object_id AND (i2.objecttype_id = 2) INNER JOIN icinga_services i3 ON i2.object_id = i3.service_object_id INNER JOIN icinga_hosts i4 ON i3.host_object_id = i4.host_object_id INNER JOIN icinga_objects i5 ON i4.host_object_id = i5.object_id INNER JOIN icinga_instances i6 ON i.instance_id = i6.instance_id WHERE (i3.config_type = '1') ORDER BY i.state_time DESC LIMIT 25; | |
+----+-------------+-------+--------+----------------------------------------------------------------------------+-------------------------------+---------+--------------------------+-------+---------------------------------+ | |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | |
+----+-------------+-------+--------+----------------------------------------------------------------------------+-------------------------------+---------+--------------------------+-------+---------------------------------+ | |
| 1 | SIMPLE | i6 | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using temporary; Using filesort | | |
| 1 | SIMPLE | i | ref | statehist_i_id_o_id_s_ty_s_ti,statehist_state_idx,sla_idx_sthist | statehist_i_id_o_id_s_ty_s_ti | 9 | icinga.i6.instance_id | 79974 | Using where | | |
| 1 | SIMPLE | i2 | eq_ref | PRIMARY,objecttype_id,objects_objtype_id_idx,sla_idx_obj | PRIMARY | 8 | icinga.i.object_id | 1 | Using where | | |
| 1 | SIMPLE | i3 | ref | service_object_id,services_host_object_id_idx,services_combined_object_idx | service_object_id | 9 | icinga.i.object_id | 1 | Using where | | |
| 1 | SIMPLE | i5 | eq_ref | PRIMARY | PRIMARY | 8 | icinga.i3.host_object_id | 1 | | | |
| 1 | SIMPLE | i4 | ref | host_object_id,hosts_host_object_id_idx | host_object_id | 9 | icinga.i3.host_object_id | 1 | Using where | | |
+----+-------------+-------+--------+----------------------------------------------------------------------------+-------------------------------+---------+--------------------------+-------+---------------------------------+ | |
6 rows in set (0.00 sec) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
| icinga_statehistory | CREATE TABLE
icinga_statehistory
(statehistory_id
bigint(20) unsigned NOT NULL AUTO_INCREMENT,instance_id
bigint(20) unsigned DEFAULT '0',state_time
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',state_time_usec
int(11) DEFAULT '0',object_id
bigint(20) unsigned DEFAULT '0',state_change
smallint(6) DEFAULT '0',state
smallint(6) DEFAULT '0',state_type
smallint(6) DEFAULT '0',current_check_attempt
smallint(6) DEFAULT '0',max_check_attempts
smallint(6) DEFAULT '0',last_state
smallint(6) DEFAULT '0',last_hard_state
smallint(6) DEFAULT '0',output
text,long_output
text,PRIMARY KEY (
statehistory_id
),KEY
statehist_i_id_o_id_s_ty_s_ti
(instance_id
,object_id
,state_type
,state_time
),KEY
statehist_state_idx
(object_id
,state
),KEY
sla_idx_sthist
(object_id
,state_time
)) ENGINE=InnoDB AUTO_INCREMENT=4149445 DEFAULT CHARSET=latin1 COMMENT='Historical host and service state changes' |