MySQL Forums
Forum List  »  Newbie

Re: SELF Join using datetime columns, Performance Issues
Posted by: Pantheos Max
Date: February 04, 2014 02:02AM

Hello,

thx for answering!

edit: I just noticed the topic said "Inner Join" - obviously im talking about a SELF join from table state_history onto itself. Sorry bout that!

@Peter:
Yes - the structure you describe is approximately the structure i want to transfer the data from the original structure to.
But the structure described above is set, i cant change anything about it (maybe i wasn't clear enough about that). Question is, is there a SELECT statement i can use on the first structure to get a result set that resembles the structure you mentioned?

@Rick:
I used one single servicecheck for testing, "12345" in my SELECT statement. My mistake here, as i used a String in the example provided above. Consider "Servicecheck" as an INT(11).
I will have another table "Servicechecks" where i select numerous servicechecks and join them via servicecheck_id to the state_history table. (I just omitted that to keep the question more simple)


The Basic idea with my SELECT is:

I have to start_date at CRITICAL HARD and end_date at the next OK HARD (per servicecheck)

I select the datetime when the servicecheck went CRITICAL HARD
start.status="Critical" AND start.status_type="Hard"
Then i self join (servicecheckid=servicecheckid) it with all entries *after* that datetime
end.datetime>start.datetime
that are HARD OK
end.status="ok" AND end.status_type="hard"

By now, i have a start_date, and all possible end_dates afterwards - also those that "belong" to other start_dates.
So now, i choose the MIN of all these end_dates, because it is the nearest to my start_date
MIN(end.datetime) AS end_datetime,
GROUP BY start.datetime

(Keep in mind, there can be numerous entries of "UNKNOWN" / "WARNING" between first occurence of CRITICAL HARD and next OK HARD)

This actually works (i think), i tested it with ~20 specific servicechecks.
But the performance is quite bad (as Peter said, SQL is not good with date/times) and it ... feels kinda makeshift / forced.

Hope i could clarify!
Regards



Edited 2 time(s). Last edit at 02/04/2014 02:09AM by Pantheos Max.

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.