CODEWITHSUNDEEP
mysqlsqlaggregate-functions
tgoossens
tgoossens

Reputation: 9856

What index or other database to use to speed this aggregating query up

I have stored a time series in a MySQL table [int(11) int(11)] The first column is epoch time in milliseconds, the second column is the value at that time.

There are 25920000 records. When I want to plot the data, I don't want to plot every millisecond but only at a certain resolution, I use the following query:

Average per day

 SELECT AVG(value) FROM measurements GROUP BY ts DIV (1000*60*60*24)

This takes already 1,5 minutes which is way longer than I want to achieve.

Is there a certain index I might be able to use to speed up this query?

Or is there maybe another DBMS that is better suited for this.

as requested in comments:

Original CREATE TABLE

CREATE TABLE measurements (ts INT(11), value INT(11))

Sample Values

15151,11
15152,15
15153,50
15154,100
....

Note that I'm currently experimenting with integer data, in the future it will be floating point data

Upvotes: 0

Views: 63

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can also use PERSISTENT Columns that will be generate the date on the fly and also has an index

CREATE TABLE `measurements ` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `datum` date AS (DATE(ts)) PERSISTENT,
  `ts` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `datum` (`datum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 2

Olli
Olli

Reputation: 1738

to speed up things (and to make sure an index might be used successful) you could add different columns for your resolutions and precalculate those from the timestamp value. After that, add indexes to this (these) columns and it should be faster since you do not have to calculate all the values before grouping with it.

i know its not the best flexible way but probably a good tradeoff.

Edit: If you fill this table you can also precalculate those values right off, so no need to recalculate lots of data.

Upvotes: 1

Related Questions