MySQL Forums
Forum List  »  Newbie

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.

Options: ReplyQuote


Subject
Written By
Posted
X Y
November 24, 2014 07:08AM
X Y
November 24, 2014 07:16AM
Re: Query Performance of History Table
X Y
November 26, 2014 08:12AM
X Y
November 27, 2014 02:56AM


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.