I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorized into groups. The events have a start and end timestamp and a process group identifier.

The table structure is as follows (InnoDB, MariaDB 10):

Table Name: measurements

 CREATE TABLE `measurements` (
 `row_id` int(11) NOT NULL AUTO_INCREMENT,
 `process_name` varchar(100) COLLATE utf8_bin NOT NULL,
 `process_id` int(11) NOT NULL,
 `process_group_id` tinyint(4) NOT NULL,
 `measurement_1` float NOT NULL,
 `measurement_2` float NOT NULL,
 `measurement_3` float NOT NULL,
 `measurement_4` float NOT NULL,
 `start_timestamp` int(11) NOT NULL,
 `end_timestamp` int(11) NOT NULL,
 PRIMARY KEY (`row_id`),
 KEY `process_group_id` (`process_group_id`,
 KEY `process_id` (`process_id`)

I'm designing a query to obtain the sum of measurements 1,2,3 & 4 for all processes running within a group at a particular point in time so that the app can express each measurement for a specific process as a percentage of the total measurements in the group at that time. The start and end times of processes within a group are not synchronized and they are of variable length.

So for a process running in Group 5, at timestamp 1431388800

SELECT  SUM(measurement_1), 
    FROM  measurements
    WHERE  process_group_id = 5
      AND  1431388800 
           BETWEEN start_timestamp 
               AND end_timestamp 

This query runs, but takes around 0.5s. The table has 8m records and grows by about 30,000 a day.

I have an index on process_group_id, start_timestamp, end_timestamp. However, the query does not appear to use anything but the process_group_id part of the index. I created an additional index on process_group_id alone to check this, and once created EXPLAIN showed it using this index.

After some searching, I saw a suggestion to modify the query and add an ORDER BY clause. Having done this the query is accelerated to around 0.06s and it seems to use the full index. However, I'm unsure as to why:

SELECT  process_group_id, 
    FROM  measurements
    WHERE  process_group_id = 5
      AND  1431388800 
  BETWEEN start_timestamp 
      AND end_timestamp
    ORDER BY  process_group_id ASC 

With 30,000 new records a day that requires their shares to be calculated, 0.06s is still not particularly fast. Is there a better way of structuring either the table or designing the query to get a few orders of magnitude quicker, or is a query which matches on one column and then a range query on two others always going to be fairly slow to run?

  • Could you provide your table description as SHOW CREATE TABLE measurements\G? It makes things easier for those trying to help you. BTW, welcome to the forum.
    – Vérace
    May 12 '15 at 21:24
  • 1
    Can the time ranges overlap? (Ignoring touching at the end-points.) It makes a big difference to how solvable the problem is.
    – Rick James
    May 13 '15 at 0:07
  • Show create table structure added - thanks for the tip @vérace.
    – Esteban
    May 14 '15 at 0:14
  • @rick-james measurements can indeed overlap, Event 1 could run from 10:00:01 - 10:30:05, Event 2 could be 09:50:55 - 11:10:30 etc etc.
    – Esteban
    May 14 '15 at 0:16

(Not an answer, but too clumsy for a comment.)

have an index on process_group_id, start_timestamp, end_timestamp. However the query does not appear to use anything but the process_group_id part of the index.

It is actually using start_timestamp although it does not say so. What was the key_len? That may give a clue. Also try EXPLAIN FORMAT=JSON SELECT ....

AND  1431388800 BETWEEN start_timestamp AND end_timestamp

Turn that into the following to see if it helps:

AND  start_timestamp <= 1431388800
AND  end_timestamp   >= 1431388800

I suspect it is identical, but I am not sure.

Caution. The difference between 0.5s and 0.06s could a warmed up cache. Run timings twice. Also, use SQL_NO_CACHE to avoid the Query cache.

How wide are the timestamp ranges typically? How precise is 1431388800? The values sound like they have a resolution of 1 second. What if we switched to 1 minute or 1 hour?

After you provide some answers, I will possibly suggest turning this into a Data Warehouse application and discuss Summary tables.


Consider this approach to storing the data. (I still don't have enough details to determine what variant of the following would be optimal.)

Since you have a "processing" phase that leads to the table in question, I suggest rewriting it to store into a different table (either in place of the existing one, or in addition)

    process_group_id ...
    ts TIMESTAMP NOT NULL, -- rounded to the minute
    sum_1 FLOAT  -- see below
    sum_2 ...,
    PRIMARY KEY(process_group_id, ts)

The table contains one row per minute. That's 0.5M per year, not terribly big. If converting from the existing structure do sum_1 += measurement_1 for each row BETWEEN starttime AND endtime.

That is possibly more processing than you are currently doing, but it should not be excessive. And it makes the SELECT extremely efficient:

SELECT  sum_1, sum_2, sum_3, sum_4
    FROM  ByMinute
    WHERE  process_group_id = 5
      AND  ts = somehow round 1431388800 to a minute

You currently have a daily dump. The processing should be obvious. If you switch to "streaming" and use the ping-pong method I mentioned, then very similar code can be used for each transient table. And you would probably have nearly up-to-the-second data all the time.

  • I tried using the comparators instead of BETWEEN - no discernible difference in speed. Likewise with the cache, I've disabled it with SQL_NO_CACHE and run different queries with the same structure to avoid that. The resolution could be lowered to minute intervals I guess. Though events in a process group do not start and end at the same points in time, using minute resolution wouldn't necessarily harm the calculations. For a test query: key_len = 4, rows = 855212. There's no explain format=json in MariaDB 10 I'm afraid.
    – Esteban
    May 14 '15 at 0:28
  • I have two thoughts on how to optimize this, but first another question: When, relative to start_timestamp and end_timestamp, do you INSERT the row with the measurements?
    – Rick James
    May 14 '15 at 5:05
  • Measurements for the previous 24 hours are stored into JSON files and delivered to a central store once the day rolls over into the next. The files are then processed in a batch - the read and insert takes a few minutes, the calculation currently takes about ~35 minutes. It's not the end of the world, but does mean that if the data supply becomes streaming/realtime at any point in the future, these calculations can't be done on the fly.
    – Esteban
    May 15 '15 at 22:04
  • Yes, you can do the calculations "on the fly". However, it is a bit more complicated. I would suggest storing the "raw" data in a transient table, then processing them. This can be especially efficient by ping-ponging a pair of tables as discussed in my ingestion blog.
    – Rick James
    May 16 '15 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.