Hi,
On Tue, Oct 2, 2018 at 11:06 PM Lukasz Szybalski <[email protected]> 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
--
Jun Omae <[email protected]> (大前 潤)
--
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.
