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: [email protected] [mailto:[email protected]] On Behalf Of Lukasz Szybalski Sent: Thursday, June 25, 2009 12:06 PM To: [email protected] 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/trac-users?hl=en -~----------~----~----~----~------~----~------~--~---
