Optimize fetching many rows from table

Question

Problem

I have the following table definition:

id          BIGINT
resource_id INT
timestamp   DATETIME
data        JSON

The primary key is id and I have an index on (resource_id, timestamp). The data is a json object (3KB), there are 5 parameters in this object of type FLOAT (among other things) that are fetched regularly. The table size is roughly 60GB and 15,000,000 rows. Data is recorded per minute for each resource id.

I want to optimize the following query:

SELECT
   resource_id
   timestamp
   data->'$.p1' /* can also include the other parameters in the select p2-p5 */
FROM table
WHERE resource_id = ? AND timestamp BETWEEN ? AND ?;

What I tried

• Creating a virtual column per parameter and creating an index for each of them (resource_id, timestamp, pX). This improves the query significantly from 70s to 8s (for fetching 6 months) but I'm worried about the number of indexes slowing down my inserts. Also, I can no longer include multiple parameters in the query since only one index can be used.

I also looked into partitioning by the resource_id but I'm not sure if that will help since mysql limits partitions to 8192 and the resources could easily exceed that.

Any idea on how to fetch this data as fast as mysql allows?

Answer 1

Storing data in a denormalized format like JSON is usually not great performance-wise when read from, especially on large amounts of data. So normalizing the JSON data into individual columns per parameter is a step in the right direction. Presumably you created 5 indexes since there's 5 parameters, right?...5 indexes on a single table is roughly the limit I aim for, but it's not a hard rule. Only way to really know if they negatively impact your write performance is to test it.

But I believe you shouldn't even need to create so many indexes since you're not using the parameters in any predicates (JOIN, WHERE, or HAVING clauses), you're just SELECTing them in your query. Instead, as long as your index covers all the fields, it's applicable to each iteration of the query. I believe a single index defined as (resource_id, timestamp, p1, p2, p3, p4, p5) should cover you.

Furthermore, if the field combination of (resource_id, timestamp) are guaranteed unique, then you can define them as the primary key instead, which will cause them to be the clustered index of the table. This will order the actual table itself accordingly, and cover you for when you want to read any of the parameter columns.