On Mar 21, 2012, at 4:29 AM, David Roe wrote: > Hi everyone, > I've reorganized our trac reports, added a bunch of new ones and changed the > View Tickets button so that it points to > http://trac.sagemath.org/sage_trac/wiki/TicketReports. If you have any ideas > for new reports you can either add them (if you can express them as a trac > query) or reply here (if they need database access). > > There are a couple more queries I'd like to add, but that are currently too > slow with the database setup (and my limited SQL skills). If anyone has any > suggestions for the following I'd like to hear them. > > * A report listing tickets containing an attachment, sorted so that the > authors who have contributed the fewest times appear first. New contributors > often don't know many other developers and can get discouraged when nobody > looks at their contribution. The following SQL query is a start, but it's > already really slow (more than a minute). I don't know if the problem is the > 50000 spam accounts in the session table or something else. > SELECT sid, > (SELECT COUNT(*) FROM ticket_change WHERE field='comment' AND author=sid) > as changes > FROM session > WHERE authenticated = 1 > ORDER BY changes
Subselects can be very slow. You might try using a join like (untested) SELECT session.sid as sid, COUNT(*) as changes FROM session LEFT INNER JOIN ticket_change ON session.sid = ticket_change.author and session.authenticated = 1 and ticket_change.field='comment' ORDER BY changes -Ivan -- To post to this group, send an email to sage-devel@googlegroups.com To unsubscribe from this group, send an email to sage-devel+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sage-devel URL: http://www.sagemath.org
