MySQL Forums
Forum List  »  Newbie

Re: Query Performance of History Table
Posted by: Peter Brawley
Date: November 24, 2014 12:33PM

Before analysing the query, there are issues with the tables ...

InnoDB offers many advantages, why not use it?

> `ticket_id` bigint(20) NOT NULL,
> `article_id` bigint(20) DEFAULT NULL,

Unless you expect 9 trillion of these IDs, and are happy to waste the unused space of another 9 trillion negative BigInt values, surely the 4 billion possible values provided by Int Unsigned would be sufficient?

Again in the interests of efficiency, wouldn't type_id, priority_id, state_id and valid_id fit in a TinyInt (one byte)?

Best to track current-era datetimes with timestamp columns.

A query clause can use one index at a time, so multiple one-column indexes won't help with queries like the one you post.

About the query ...

To run your query against test data, we'd need some Inserts for ticket_history.

Join queries written with explicit join syntax are easier to write, debug and maintain using explicit join syntax (... from a join b on ...).

MySQL does not optimise In( Select ...) at all well, so after you fix the tables, best rewrite that clause, eg ...

select s.name, count(h.state_id) as StateIds
from ticket_history h
join (
  select ticket_id, max(ticket_history.id) as last
  from ticket_history 
  where state_id in (1, 4, 10, 12, 13) and change_time >= '2014-10-30' 
  group by ticket_id
) as lasts on h.id=lasts.last  
join ticket_state s on s.id = h.state_id 
group by h.state_id;

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
November 24, 2014 12:33PM
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.