CODEWITHSUNDEEP
mysqlsql
MAX POWER
MAX POWER

Reputation: 5458

MySQL - group by interval query optimisation

Some background first. We have a MySQL database with a "live currency" table. We use an API to pull the latest currency values for different currencies, every 5 seconds. The table currently has over 8 million rows.

Structure of the table is as follows:

id (INT 11 PK)
currency (VARCHAR 8)
value (DECIMAL
timestamp (TIMESTAMP)

Now we are trying to use this table to plot the data on a graph. We are going to have various different graphs, e.g: Live, Hourly, Daily, Weekly, Monthly.

I'm having a bit of trouble with the query. Using the Weekly graph as an example, I want to output data from the last 7 days, in 15 minute intervals. So here is how I have attempted it:

SELECT *
FROM currency_data
WHERE ((currency = 'GBP')) AND (timestamp > '2017-09-20 12:29:09')
GROUP BY UNIX_TIMESTAMP(timestamp) DIV (15 * 60)
ORDER BY id DESC

This outputs the data I want, but the query is extremely slow. I have a feeling the GROUP BY clause is the cause.

Also BTW I have switched off the sql mode 'ONLY_FULL_GROUP_BY' as it was forcing me to group by id as well, which was returning incorrect results.

Does anyone know of a better way of doing this query which will reduce the time taken to run the query?

Upvotes: 0

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You may want to create summary tables for each of the graphs you want to do.

If your data really is coming every 5 seconds, you can attempt something like:

SELECT *
FROM currency_data cd
WHERE currency = 'GBP' AND
      timestamp > '2017-09-20 12:29:09' AND
      UNIX_TIMESTAMP(timestamp) MOD (15 * 60) BETWEEN 0 AND 4
ORDER BY id DESC;

For both this query and your original query, you want an index on currency_data(currency, timestamp, id).

Upvotes: 1

Related Questions