Tickets :: Comment on [#4893] Abysmal performance when computing date_assigned, date_resolved, and date

* Your Email Address
* Spam protection	Enter the letters below: .__ .__ . .. ..___ [ __[__)\| \|\| \|[__ [_./[__)\|/\\|\|/\\|\|
Comment	> Running MySQL 4.1.20-log, PHP 5.1.4, and Whups 1.0-cvs with the > following table counts: > > whups_tickets: 202 > > whups_logs: 10809 > > > > We noticed that any queries joining against whups_logs to compute > date_assigned, date_updated, and date_resolved were incredibly slow > (ex: My Tickets and Quick Searches). Based on the nature of the join > (text), and after adding indexes to all tables, and tweaking MySQL as > far as possible, we patched Whups 1.0 to store (rather than > recompute) the date_assigned, date_updated, and date_resolved fields > each time. > > > > SQL and code patches follow below. We hope you find them useful and > will integrate them into CVS (because we use this in a production > environment, we tend to stay 6 months or so behind releases; so, > these patches may not be against the latest whups): > > > > The SQL to alter the schema is: > > alter table whups_tickets add column date_updated int; > > alter table whups_tickets add column date_assigned int; > > alter table whups_tickets add column date_resolved int; > > > > The SQL to back-fill the stored columns with freshly computed values is: > > update whups_tickets as T1 set date_updated = (select > max(whups_logs.log_timestamp) from whups_logs where > whups_logs.ticket_id = T1.ticket_id group by whups_logs.ticket_id); > > > > update whups_tickets as T1 set date_assigned =(select > max(log_timestamp) from whups_logs inner join whups_states on > whups_logs.log_type='state' and > whups_logs.log_value_num=whups_states.state_id and > whups_states.state_category='assigned' where > whups_logs.ticket_id=T1.ticket_id group by whups_logs.ticket_id); > > > > update whups_tickets as T1 set date_resolved =(select > max(log_timestamp) from whups_logs inner join whups_states on > whups_logs.log_type='state' and > whups_logs.log_value_num=whups_states.state_id and > whups_states.state_category='resolved' where > whups_logs.ticket_id=T1.ticket_id group by whups_logs.ticket_id); > > > > The code patches are attached.
Attachment
Watch this ticket

Saved Queries