Re: Query Performance of History Table
Posted by:
X Y
Date: November 26, 2014 08:12AM
Hello Peter,
thanks a lot for you reply, it helped a lot.
On the tables: i didn't create them myself, they were created by the ticket system software we are using so i'd rather not change the table structure. But definately a good hint i will keep in mind when i create my own tables in the future.
The query you provided worked perfectly performance wise (it runs in under 1 second). I wasn't aware that mysql does not optimize interlaced select statements that well. I tested a similar query on a oracle database and it was much faster. That explains it, so thanks again!
Unfortunately since i could now actually test my query, I discovered a flaw in my logic: I am currently selecting the max id value of the history table but only for the state_ids given, which means if there is a higher id value present in the table that does not fit the state_id criteria it will be omited, which means almost every ticket is included in the result, because somewhere in the past it had a state that matches, but it's not the the newest entry in the table. That's not what I wanted. Instead i would like to determine the max id first than check if it fits the state id criteria, if it does it should be included in the result, if not, it should be left out completely.
I hope i made myself clear and hope somebody out there can help me with that problem.
Subject
Written By
Posted
November 24, 2014 07:08AM
November 24, 2014 07:16AM
November 24, 2014 12:33PM
Re: Query Performance of History Table
November 26, 2014 08:12AM
November 26, 2014 11:33AM
November 27, 2014 02:56AM
November 27, 2014 09:19AM
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.