Done in HEAD with not too many tweaks. Thanks!

Sounds good. I'll look at the code patches and see how off they are; 
if I can't get it to apply with some massaging I'll ask you to take a 
look at the latest CVS.

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.