MySQL Forums
Forum List  »  Partitioning

Help with partition strategy
Posted by: Nuno Donato
Date: January 28, 2020 03:47AM

Hi everyone!

I'm trying to find out what would be the best partition strategy for a table that has millions of rows. The relevant columns are

* date
* userid
* key
* value

Ke-values are received every X min from sensors, and there are N key-value pairs per user. (meaning, every time data is received, it gets more than 1 key-value pair per user).

The table grows big very fast, but most users only need to access the last month(or year) of data (to generate charts and stats). However, I need to keep all data available for people who want to see historic data.

My first thought was to partition based on userid first and date second (using a range rule to split the partitions).

My other option was to actually have a scheduled event to move older rows (>2years) to an "archive" table. Both the current and archive table would be partitioned based on userid only.

What would be the preferred strategy here? Any other valuable tips? Is it worth to actually simulate both scenarios and see which performs better?

thanks in advance

Options: ReplyQuote

Written By
Help with partition strategy
January 28, 2020 03:47AM
January 28, 2020 09:55AM
January 28, 2020 10:12AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.