I’ve probably said it before, but in my current job I get to learn a lot of new things all the time, as a web developer I’ve self-taught myself a lot of my current skill set. One of which is MySQL. I had a pretty basic understanding of SQL and simplistic queries. I’ve never really pushed myself into going further into bigger and nastier queries. Though, I did pick up a good piece of advice at an open source conference I attended. The speaker mentioned that independent of whatever language you are programming in, your database engine (he was speaking of MySQL) will do it better every time. Well the more I dig into it, the more I realize just how correct that statement is. I’ve been living under the impression that I can deal with data better outside of the database engine versus generating a better query to get the right data. I know that makes me sound lazy or dumb – but I think a lot of web developers fall into this when there’s no real reason to push the limits. I mean I could probably catch a lot of you selecting * in your statements huh?

Alright enough of the brow beating…

The KACE system comes with a few stock reports that really didn’t get the information that my uppers were looking for. So they tasked me to create a few reports to get some more information. The first one was to gather the average age of tickets for each queue. Sounded simple enough right? No…not at all, well for me at least I dug into their table structure and their reporting system only to find myself over my head. I turned to my only friend in this matter Google, and started looking for examples of what I needed to do and built a query from the ground up. It’s probably not optimized as much as it should be – because it can take a long time to run (I know where the problem is, but I’m not sure how to optimize the sections).

Here’s the final query (So if you see something that could be done better let me know)

SELECT
    HD_QUEUE.NAME AS QUEUE,
    HD_CATEGORY.NAME AS CATEGORY,
    (SELECT count(HD_TICKET.ID)
        FROM HD_TICKET
        WHERE HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
        AND HD_TICKET.TIME_CLOSED != '0000-00-00 00:00:00'
        AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND HD_STATUS.STATE = 'closed') AS COUNT,
    (SELECT TIME_FORMAT(SEC_TO_TIME(AVG(UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED) - UNIX_TIMESTAMP(HD_TICKET.CREATED))),'%Hh %im %ss') AS AVERAGE_TIME
        FROM HD_TICKET
        WHERE HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
        AND HD_TICKET.TIME_CLOSED != '0000-00-00 00:00:00'
        AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
        AND HD_STATUS.STATE = 'closed') AS AVE_AGE
FROM
    HD_TICKET
LEFT JOIN HD_QUEUE on HD_QUEUE_ID = HD_QUEUE.ID
LEFT JOIN HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE
    HD_TICKET.HD_CATEGORY_ID IN (SELECT ID FROM HD_CATEGORY)
    AND HD_TICKET.TIME_CLOSED != '0000-00-00 00:00:00'
    AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND HD_STATUS.STATE = 'closed'
GROUP BY CATEGORY
ORDER BY HD_QUEUE.NAME, HD_CATEGORY.NAME

So for the 30 day interval it can take upwards of 100 seconds, out of 11k records – I suspect the problem area is the two sub-queries at the top. Another caveat here is working with the KACE’s java based reporting system, there is a problem with it recognizing the result from the average age, so I had to wrap it in the TIME_FORMAT to fix it.

I found most of my answers like I said using Google. Most of those results lead me back to MySQL’s documentation with a smattering of results in one of my favorite sites Stack Overflow.

I also used the MySQL Workbench tool to develop the queries which helped a bunch.

Stay tuned for more queries.

UPDATE!!!

So I spent sometime this morning looking through my versions of this query and found one that ran faster, but I was concerned because it displayed different results. I looked at another query I had built and found that I could match the results against the results that were different. So what I’m trying to say is I could corroborate the results from the quicker version of the sql with another query thereby giving more reliability on the quicker version of the SQL.

Here is the revised query.

SELECT
    HD_QUEUE.NAME AS QUEUE,
    HD_CATEGORY.NAME AS CATEGORY,
    count(HD_TICKET.ID) AS COUNT,
    TIME_FORMAT(SEC_TO_TIME(AVG(UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED) - UNIX_TIMESTAMP(HD_TICKET.CREATED))),'%Hh %im %ss') AS AVERAGE_TIME

FROM
    HD_TICKET
LEFT JOIN HD_QUEUE on HD_QUEUE_ID = HD_QUEUE.ID
LEFT JOIN HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE
    HD_TICKET.HD_CATEGORY_ID IN (SELECT ID FROM HD_CATEGORY)
    AND HD_TICKET.TIME_CLOSED != '0000-00-00 00:00:00'
    AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND HD_STATUS.STATE = 'closed'
GROUP BY CATEGORY
ORDER BY HD_QUEUE.NAME, HD_CATEGORY.NAME

This query runs in less than a second.