Hello again Rick & Peter & everyone else interested
@Peter: Sadly, the source Table(s) are not mine to control / alter. Have to work with what i have. Maybe adding indexes is possible.
@Rick & Peter
SHOW CREATE TABLE:
'CREATE TABLE `state_history` (
`datetime` datetime NOT NULL,
`datetime_usec` int(11) NOT NULL,
`servicecheck` int(11) NOT NULL,
`status` enum(''OK'',''WARNING'',''CRITICAL'',''UNKNOWN'') NOT NULL,
`status_type` enum(''SOFT'',''HARD'') NOT NULL,
`prior_status_datetime` datetime NOT NULL,
`prior_status` enum(''OK'',''WARNING'',''CRITICAL'',''UNKNOWN'',''INDETERMINATE'') NOT NULL,
`output` text NOT NULL,
KEY `datetime` (`datetime`,`servicecheck`),
KEY `state_history_servicecheck_fk` (`servicecheck`),
CONSTRAINT `state_history_servicecheck_fk` FOREIGN KEY (`servicecheck`) REFERENCES `servicechecks` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'
(This is the Table i wrote up manually in my original question)
Joining Tables: Hosts
'CREATE TABLE `hosts` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`alias` varchar(255) default NULL,
`hostgroup1` varchar(128) default NULL,
`hostgroup2` varchar(128) default NULL,
`hostgroup3` varchar(128) default NULL,
`hostgroup4` varchar(128) default NULL,
`hostgroup5` varchar(128) default NULL,
`hostgroup6` varchar(128) default NULL,
`hostgroup7` varchar(128) default NULL,
`hostgroup8` varchar(128) default NULL,
`hostgroup9` varchar(128) default NULL,
`hostgroup` varchar(128) default NULL,
`nagios_object_id` int(11) NOT NULL,
`monitored_by` varchar(128) default NULL,
`active_date` int(11) NOT NULL,
`crc` int(11) default NULL,
`most_recent` tinyint(1) default NULL,
`opsview_instance_id` smallint(6) default ''1'',
PRIMARY KEY (`id`),
UNIQUE KEY `active_date` (`active_date`,`name`),
KEY `name` (`name`),
KEY `nagios_object_id` (`nagios_object_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3084 DEFAULT CHARSET=latin1'
Joining Tables: Servicechecks
'CREATE TABLE `servicechecks` (
`id` int(11) NOT NULL auto_increment,
`hostname` varchar(128) NOT NULL,
`name` varchar(128) NOT NULL,
`host` int(11) NOT NULL,
`nagios_object_id` int(11) NOT NULL,
`description` varchar(255) default NULL,
`servicegroup` varchar(128) default NULL,
`keywords` text,
`active_date` int(11) NOT NULL,
`crc` int(11) default NULL,
`most_recent` tinyint(1) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `host` (`host`),
KEY `nagios_object_id` (`nagios_object_id`),
CONSTRAINT `servicecheck_host_fk` FOREIGN KEY (`host`) REFERENCES `hosts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38513 DEFAULT CHARSET=latin1'
I now use this Query:
select h.name, s.name, start.datetime as start_datetime, min(end.datetime) as end_datetime,
(unix_timestamp(min(end.datetime))-unix_timestamp(start.datetime)) as duration, start.output, end.output
from hosts h
left join servicechecks s on h.id=s.host
left join state_history start on s.id=start.servicecheck
join state_history end on start.servicecheck=end.servicecheck
and end.datetime>start.datetime
and end.status="ok" and end.status_type="HARD"
where start.status="CRITICAL" and start.status_type="HARD"
and start.datetime between "2013-12-01 00:00:00" and "2013-12-31 23:59:59"
group by start.datetime
I need the hostname (hosts.name) as well as the servicecheck name (servicecheck.name) in the ResultSet;
This is the extended explain of the query:
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'h', 'index', 'PRIMARY', 'name', '257', NULL, '3111', 'Using index; Using temporary; Using filesort'
'1', 'SIMPLE', 's', 'ref', 'PRIMARY,host', 'host', '4', 'odw.h.id', '5', 'Using where'
'1', 'SIMPLE', 'start', 'ref', 'datetime,state_history_servicecheck_fk', 'state_history_servicecheck_fk', '4', 'odw.s.id', '22', 'Using where'
'1', 'SIMPLE', 'end', 'ref', 'datetime,state_history_servicecheck_fk', 'state_history_servicecheck_fk', '4', 'odw.start.servicecheck', '57', 'Using where'
Takes now ~ 70 seconds to return 8000+ rows.
It's manageable, but it still feels kinda "clumsy", especially the self join
Regards
Stefan