[prev in list] [next in list] [prev in thread] [next in thread] 

List:       trac
Subject:    [Trac] Re: reports available
From:       "Dan Winslow" <DWinslow () aiminstitute ! org>
Date:       2009-06-25 18:39:25
Message-ID: F276036850359C4D8F93F7B19189838203CC1489 () exchmbox ! omaha ! org
[Download RAW message or body]


One thing that comes to mind immediately is that you could add some
indexes. Not sure what the default indexing is like on mysql but if it's
like it is on SQLITE it could use some tweaking.

The time killer in this query is probably all the full table scans of
the ticket_change table. I would think a join might help out speed-wise,
as it could throw out all the rows that don't match the ticket. Or even
just a single ( select * from ticket_change tc where t.id=tc.id )
subselect and pull the columns from there instead of re-scanning the
whole table for each separate column you want to build. Just some
suggestions, I'm not a SQL expert.

I know you asked for the actual SQL statement, but I don't have your
data and I don't run on mysql.

-----Original Message-----
From: trac-users@googlegroups.com [mailto:trac-users@googlegroups.com]
On Behalf Of Lukasz Szybalski
Sent: Thursday, June 25, 2009 12:06 PM
To: trac-users@googlegroups.com
Subject: [Trac] reports available


Hello,

http://trac.edgewall.org/ticket/8354

I've opened a ticket to add more "management" reports to trac. We have
a high value of tasks that is being tracked so reports listed in above
ticket are crucial.

Here is one I need help optimizing....(500 tickets a day, 4000 in 8
days since we started)

My Recently closed tickets. Is there a way to optimized it better in
mysql? Can somebody run this in mysql query browser and see if you can
modify it to run
faster? I'm looking for actual final sql statement for mysql.

Thanks,
Lucas


select id, summary, component, milestone, owner ,
(select tc.author from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue =
'closed' order by tc.time DESC limit 1) as ModifiedBy,
  (TIME(FROM_UNIXTIME((select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue =
'closed')))) as closetime,
  (DATE(FROM_UNIXTIME((select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue =
'closed')))) as closedate
  from ticket t
where t.status = 'closed' and (select tc.author from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue =
'closed' order by tc.time DESC limit 1) = '$USER' and (select
MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue =
'closed')  > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
  order by closedate DESC,closetime DESC



-- 
Using rsync. How to setup rsyncd.
http://lucasmanual.com/mywiki/rsync
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Trac Users" group.
To post to this group, send email to trac-users@googlegroups.com
To unsubscribe from this group, send email to trac-users+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/trac-users?hl=en
-~----------~----~----~----~------~----~------~--~---



[prev in list] [next in list] [prev in thread] [next in thread]