MySQL – Tracking Tickets Created and Resolved per Year

aggregateMySQLmysql-5.5

In a third-party application (RT4) running on MySQL 5.5 I have a series of helpdesk tickets represented as records in a table. Each record has a date/time created, a date/time resolved, and a merge flag (NULL or 1).

id int(11) AI PK, Created datetime, Resolved datetime, IsMerged smallint(6)

When the ticket is created the created field is populated and the resolved field and merge flag are NULL. When the ticket is closed the resolved field is populated. If the ticket is a duplicate the merge flag is set to 1, and the resolved date is set to 1970-01-01 00:00:00.

I've looked at a number of the offered related answers and of those mysql: counting number of tickets which are open per day basis seemed the most promising. I feel it's almost what I need but I can't extrapolate from the answers there to my use case.

I can list the number of tickets created per year easily enough –

select year(Created) as Created_Yr, count(*) as N_Tickets from rt4.Tickets
group by year(Created)
order by 1

A similar query can list the set of non-merged tickets per year –

select year(Resolved) as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where Resolved is not null
group by year(Resolved)
order by 1

However, what I would like is to list the number of tickets resolved or merged, per year.

I can do this year by year like this –

select 2016 as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where ( Resolved >= '2016-01-01' and Resolved < '2017-01-01' )
or ( Created >= '2016-01-01' and Created < '2017-01-01' and IsMerged = 1 )

We have data from years 2012 to 2017 and I'm hoping to avoid having to run a query per year and manually collate the results.

Is there a query that will list the Resolved year (or the Created year if the ticket was merged), for all years present in the database?

Best Answer

SELECT * 
  FROM ( SELECT DISTINCT year(Created) AS Created
           FROM rt4.Tickets
       ) AS w

  LEFT JOIN ( SELECT year(Resolved) AS Resolved
                   , count(*) AS resolved_cnt
                FROM rt4.Tickets
               WHERE Resolved IS NOT NULL
               GROUP BY year(Resolved) 
            ) AS z
         ON z.Resolved = w.Created

  LEFT JOIN ( SELECT year(Created) AS Created
                   , count(*) AS merged_cnt
                FROM rt4.Tickets
               WHERE Merged IS NOT NULL
               GROUP BY year(Created) 
            ) AS q
         ON q.Created = w.Created
;

Sure you have to remember that functional restrictions like year() in the WHERE, JOIN...ON or GROUP BY clauses cannot be speeded up by indexes and can be VERY slow.

I hope that query wouldn't be runned on the regular basis.

UPDATE 2012-02-21 17:06 EDT

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

@Sebastian, I put the query back in the original join order. Please try this as well !!!

UPDATE 2012-02-21 17:11 EDT

Make sure the datetime field is indexed

ALTER TABLE TAB_MESSAGE ADD INDEX read_datetime_ndx (read,datetime);

MySQL optimization – year column grouping – using temporary table, filesort

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided. But, those aren't the real problem. The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too. So it may come down to whether you want to pay for it now or pay for it later.

Best Answer

Related Solutions

Mysql – Limit WHERE to MAX() & COUNT()

UPDATE 2012-02-21 17:06 EDT

UPDATE 2012-02-21 17:11 EDT

MySQL optimization – year column grouping – using temporary table, filesort

Related Question