2

I have a MySQL table which contains historic product values. A new record per product is updates every minute. The timestamp field is an INTEGER field with a unix timestamp. There is an index on the timestamp column.

If we visualize the values based for each hour we use

WHERE MOD(history.timestamp, 3600) = 0

Now we want to calculate an historic 7 day average on the hourly results.

My first attempt is the following:

SELECT 
    history_1m.timestamp,
    history_1m.value,
    (
        SELECT 
            AVG(avg1_internal.close)
        FROM history_1m as avg1_internal
        WHERE
            avg1_internal.productid = 1 AND
            avg1_internal.timestamp <= history_1m.timestamp AND
            avg1_internal.timestamp > (history_1m.timestamp - (84600 * 7)) AND
            MOD(avg1_internal.timestamp, 3600) = 0
    ) AS 7d_avg
FROM history_1m
WHERE 
    MOD(history_1m.timestamp, 3600) = 0 AND 
    history_1m.productid = 1 AND 
    timestamp >= 1616590800 AND 
    timestamp <= (1616590800 + (180 * 3600))

This query takes approx. 8 seconds to run. However if we run the subquery seperatly it completes a single query in +- 0,0064 seconds.

My tought was that it tried to calculate the historic 7d average for all rows and later filtering them for the specified time range. (180 hours)

So i tried the following:

SELECT 
    prefetch.timestamp,
    prefetch.value,
    (
        SELECT 
            AVG(avg1_internal.close)
        FROM history_1m as avg1_internal
        WHERE
            avg1_internal.productid = 1 AND
            avg1_internal.timestamp <= prefetch.timestamp AND
            avg1_internal.timestamp > (prefetch.timestamp - (84600 * 7)) AND
            MOD(avg1_internal.timestamp, 3600) = 0
    ) AS 7d_avg
FROM (
        SELECT 
            prefetch_data.timestamp, 
            prefetch_data.value,
        FROM history_1m AS prefetch_data
        WHERE 
            MOD(prefetch_data.timestamp, 3600) = 0 AND 
            prefetch_data.productid = 1 AND 
            timestamp >= 1616590800 AND 
            timestamp <= (1616590800 + (180 * 3600))
    ) AS prefetch

However it still takes +- 8 seconds to complete the query.

If we manually select all the desired datapoints (180 hours). And execute the subquery one by one for the datapoints the result is calculated much faster.

Is there a way to get this result in a single query statement?

3
  • perfromance questions need a lot more informatiom see dba.meta.stackexchange.com/questions/3034/…
    – nbk
    Apr 26, 2021 at 15:08
  • Also would help to see some samples of the source data and what the output should be
    – user212533
    Apr 26, 2021 at 16:02
  • MOD(ts, 3600) = 0 limits the rows to just the top of each hour? Are those the only rows you have? Or do you have 3599 seconds that you want to ignore?
    – Rick James
    Apr 26, 2021 at 20:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.