MySQL SELECT n-th row between dates (time interval)

MySQLmysql-5.5select

I have a server which records various sensor data each second and inserts it into a MySQL table.
To view the sensor data on a dashboard I have a chart which also gets updated with live data each second. The database is growing at a rate of more than 250,000rows / 24h (3 sensors+).

When loading the dashboard I want to display history data. At least 24h, more would be better (72+ ideal); my testings are all done with 24h.

The amount of data is huge (86400 rows), so I though of selecting only a subset of data for the history, which can be refined on zoom. I chose an interval of 5 minutes, or 300 seconds:

SELECT * FROM `data` WHERE `sensorId` = 0
 AND `unixDate` >= (1458829800 - 86400)
 AND `unixDate` < 1458829800
 AND NOT `unixDate` % 300 -- left out for all data
ORDER BY `id` DESC

This query is of course amazingly slow, on the damn small machine I'm running this on (a raspberry pi) it takes more than 10 seconds!

Querying all the 86400 rows takes roughly below one second.

I might be able to install a separate database server on a heavy machine as well, but I'd like to get this project done before my server environment is up and running, also I don't want to send so much data around my network each second…

I wonder two things: is there a quicker way of querying data by time intervals and might there be a completely better solution (time series db, which seemed a bit overkill to me) for this kind of work?

Best Answer

You should create a summary table that will store this summary data.

5 minute summaries will require additional 288 rows per day (24 * 60 / 5); 30 minute summaries - 48 rows per day (24 * 60 / 30); hourly summaries - 24 rows per day. In total, you're looking at storing additional 360 rows per day (288 + 48 + 60), which is a tiny overhead (360 / 86400 ~= 0.4167%) for a massive performance boost.

Try something like this:

CREATE TABLE data_summary (
    granularity_mins INT NOT NULL,
    unixDate BIGINT NOT NULL,
    sensorId INT NOT NULL,
    sensor_min_value DECIMAL NOT NULL,
    sensor_max_value DECIMAL NOT NULL,
    sensor_ave_value DECIMAL NOT NULL,
    sensor_readings_count INT NOT NULL,
    PRIMARY KEY(granularity_mins, sensorId, unixDate)
)

You can then get your 5 minute summary with a SELECT like this:

SELECT *
FROM data_summary
WHERE granularity_mins = 5
    AND unixDate > 1458829800 - 86400
    AND unixDate < 1458829800
    AND sensorId = 0

Related Solutions

Mysql – Speed inserts in database with a table with +47 million data per each year, containing more than 10 years

Simple this one. Create a staging table with the same columns as your main table. No indexes on the staging table please as this will slow it down. Insert your rows into the staging table via .NET as normal. Then run:

BEGIN TRAN
INSERT INTO main_table (cols here)
    SELECT cols here from staging_table
TRUNCATE TABLE staging_table
COMMIT

You can still run that SQL inline via .net, or simply use a stored proc. Either way, the insert into the main table will be in a SINGLE insert, rather than .nets single insert for each row (RBAR). As the above method will insert data into the main table in one go, the indexes are updated once. We use this to great effect in our warehousing databases.

As your inserting a lot of data into the main_table, an alternative is to drop all the indexes on the main_table, insert your data, then re-create the indexes. This too is a very efficient (read fast) way.

MySQL – Database Redesign for Sensor Data Collection

You should think about partitioning the table for a big reason.

All indexes you have on a giant table, even just one index, can generated a lot of CPU load and disk I/O just to perform index maintenance when executing INSERTs, UPDATEs, and DELETEs.

I wrote an earlier post back on October 7, 2011 on why Table Partitioning would be a big help. Here is one excerpt from my past post:

Partitioning of data should serve to group data that are logically and cohesively in the same class. Performance of searching each partition need not be the main consideration as long as the data is correctly grouped. Once you have achieved the logical partitioning, then concentrate on search time. If you are just separating data by id only, it is possible that many rows of data may never be accessed for reads or writes. Now, that should be a major consideration: Locate all ids most frequently accessed and partition by that. All less frequently accessed ids should reside in one big archive table that is still accessible by index lookup for that 'once in a blue moon' query.

You can read my entire post later on this.

To cut right to the chase, you need to research and find out what data is rarely used in your 10GB table. That data should be placed in an archive table that is readily accessible should you need adhoc queries for a historical nature. Migrating that archival from the 10GB, followed by OPTIMIZE TABLE on the 10GB table, can result in a Working Set that is faster to run SELECTs, INSERTs, UPDATEs, and DELETEs. Even DDL would go faster on a 2GB Working Set than a 10GB table.

UPDATE 2012-02-24 16:19 EDT

Two points to consider

From your comment, it sounds like normalization is what you may need.
You may need to migrate out everything over 90 days old into an archive table but still access archive and working set at the same time. If your data is all MyISAM, I recommend using the MERGE storage engine. First, you create the MERGE table map once that unites a working set MyISAM table and an archive MyISAM table. You would keep data less than 91 days in one MyISAM table and rollover any data over 90 days old into the archive. You would query the MERGE table map only.

Here are two posts I made on how to use it:

Here is an additional post I made on tables with a lot of columns

Too many columns in MySQL

Best Answer

Related Solutions

Mysql – Speed inserts in database with a table with +47 million data per each year, containing more than 10 years

MySQL – Database Redesign for Sensor Data Collection

UPDATE 2012-02-24 16:19 EDT

Related Question