MySQL Forums
Forum List  »  Newbie

Inner Join using datetime columns, Performance Issues
Posted by: Pantheos Max
Date: January 31, 2014 11:14AM

Hello everybody!

I have here a table schema looking quite like this
(its from the opsview data warehouse odw, table state_history, documented here ODW Documentation)
check     datetime            status   statustype prior_status
CPU Usage 2013-31-12 11:11:11 OK       HARD       UNKNOWN
CPU USAGE 2013-31-12 11:22:00 CRITICAL SOFT       OK
CPU USAGE 2013-31-12 11:23:00 CRITICAL HARD       CRITICAL
CPU USAGE 2013-31-12 11:30:20 OK       HARD       CRITICAL

prior_status_datetime
2013-01-01 00:00:00
2013-31-12 11:11:11
2013-31-12 11:22:00
2013-31-12 11:23:00

So, you can see that there is an entry everytime the status changes; it changes from OK to CRITICAL SOFT to CRITICAL HARD and then back to OK. What i want to know is:

How long did it take to get from CRITICAL HARD back to OK HARD?

It is possible to change from status CRITICAL type HARD to status UNKNOWN or status WARNING, so the OK HARD state doesnt immediately follow the CRITICAL HARD (only sometimes it does).

Additional Informations:
- There is an index on datetime:
Type BTREE
Unique NO
Columns datetime, servicecheck
- There are ~ 1.5 million rows in the table
- servicecheck column is int(11) containing mostly 4 or 5 digit integers
- statustype is always SOFT or HARD
- Possible values for status are CRITICAL, OK, WARNING or UNKNOWN.

I tried this:

SELECT start.servicecheck, start.datetime AS start_datetime, MIN(end.datetime) AS end_datetime,
(MIN(end.datetime)-start.datetime) AS duration
FROM state_history start 
JOIN state_history end ON start.servicecheck=end.servicecheck
AND end.datetime>start.datetime
AND end.status="ok" AND end.status_type="hard"
AND start.status="Critical" AND start.status_type="Hard"
AND start.servicecheck=12345
GROUP BY start.datetime
ORDER BY start.datetime;

Is this the best / most efficient way?
I tried the query with several servicecheck id and it takes 30-40 seconds.
But I have to do this not for one, but a lot of servicechecks joined in from another table; then its adding up to ~140 - 200 seconds, depending on ho many servicecheck ids i have to handle.

Any suggestions / ideas would be great (not looking for someone doing my work, just help).

Greetings

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.