[Tickets #4893] Abysmal performance when computing date_assigned, date_resolved, and date_updated (patch included)

[bugs@bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/?id=4893
-----------------------------------------------------------------------
 Ticket             | 4893
 Created By         | php at ideacode.com
 Summary            | Abysmal performance when computing date_assigned, date_resolved, and
                    | date_updated (patch included)
 Queue              | Whups
 Type               | Enhancement
 State              | New
 Priority           | 2. Medium
 Owners             | 
+New Attachment     | patches.txt
-----------------------------------------------------------------------


php at ideacode.com (2007-01-14 16:13) wrote:

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.