explain icinga query

gistfile1.txt

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)

| 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' |