MySQL Forums
Forum List  »  General

Re: Multiple complex queries
Posted by: Rick James
Date: December 04, 2009 10:50PM

The following should give you on the order of 60x speedup over your current query, whatever it is. Will that do?

I would start with a "summary" table of each hour. At the top of the next hour, summarize 60 rows into this table. All your reports can work off the summary table.
CREATE TABLE Summary (
    hr DATETIME NOT NULL  COMMENT 'truncated to start of hour',
    ct TINYINT NOT NULL  COMMENT 'number of entries (usually 60)',
    sum_temp1 FLOAT    COMMENT 'SUM(temp1) over the 60 entries',
    max_temp1 FLOAT    COMMENT 'MAX(temp1) over the 60 entries',
    etc.
    directions SET('N', 'NE', 'E', ...) COMMENT 'which directions happened',
    PRIMARY KEY(hr)
# Report:
SELECT  hr,
        SUM(sum_temp1) / SUM(ct)  AS avg_temp1,
        MAX(max_temp1)  AS max_temp1,
        ...
    FROM Summary
    WHERE hr >= '$start'
      AND hr < DATE_ADD('$start', INTERVAL 12 HOUR) -- or whatever
    ORDER BY hr
;

Hmmmm... Normally I say "do NOT store averages and do NOT do AVG(AVG(...))". But maybe it is the 'right' thing to do compute them as above. Normally you will have exactly 60 entries comprising a row. In that case, AVG(avg_temp1) is same as SUM(sum_temp1)/SUM(ct). If you missed a few readings, you might prefer to simply assume that the remaining values should be averaged for the "hourly average". OK, I convinced myself; store avg_temp1, not sum_temp1.

Why did I pick "hour"? I could have picked "6-hour" units for the summary table, but then I got to worrying about daylight savings time.

hr can be computed via some string fiddling:
+---------------------+-----------------------------------+
| now()               | concat(left(now(), 13), ':00:00') |
+---------------------+-----------------------------------+
| 2009-12-04 20:52:26 | 2009-12-04 20:00:00               |
+---------------------+-----------------------------------+

Options: ReplyQuote


Subject
Written By
Posted
December 03, 2009 05:14AM
December 03, 2009 09:02AM
Re: Multiple complex queries
December 04, 2009 10:50PM
December 07, 2009 04:16AM
December 07, 2009 10:37AM
December 07, 2009 12:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.