MySQL Forums
Forum List  »  Newbie

Re: Inner Join using datetime columns, Performance Issues
Posted by: Pantheos Max
Date: February 06, 2014 07:00AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.