On Tuesday, October 2, 2018 at 10:20:41 AM UTC-5, Jun Omae wrote: > > Hi, > > On Tue, Oct 2, 2018 at 11:06 PM Lukasz Szybalski <[email protected] > <javascript:>> wrote: > > > > Hello, > > I'm trying to run below query, but the query is timing out and takes > 7+gb of temp table storage calculation then times out. Does anybody see > what might be a problem with this query and why it doesn't perform in > memory? > > > > SELECT owner as __group__, DATE(FROM_UNIXTIME(time)) as Created,version, > milestone, count(*) AS 'Count of Tickets' > > FROM ticket > > WHERE time > UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH) and status = > 'closed' > > GROUP BY owner,DATE(FROM_UNIXTIME(time)),(milestone IS NULL), milestone > > ORDER BY owner,DATE(FROM_UNIXTIME(time)) DESC ,(milestone IS NULL), > milestone > > The time column of ticket table stores created time in microseconds. > Then, "time > UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH)" matches all > ticket records. > > Instead, please try the following query: > > SELECT owner as __group__, > DATE(FROM_UNIXTIME(time/1000000)) AS Created, > COALESCE(version, '') AS version, > COALESCE(milestone, '') AS milestone, > COUNT(*) AS 'Count of Tickets' > FROM ticket > WHERE time > (UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH) * 1000000) AND > status = 'closed' > GROUP BY 1, 2, 3, 4 > ORDER BY 1, 2 DESC, 3, 4 > >
Thanks, one more. This one times out with gateway error and it takes about 9gb of ram if I run it on workbench SELECT author, count(distinct(ticket)) AS 'Count of Tickets' FROM ticket_change WHERE time > UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY)*1000000 GROUP BY author ORDER BY count(distinct(ticket)) ASC ,author Thanks Lucas -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/trac-users. For more options, visit https://groups.google.com/d/optout.
