CODEWITHSUNDEEP
mysqlindexingdatabase-partitioning
lion
lion

Reputation: 127

MySQL partitioning because of users and timestamp date ranges

I have this case of app having lots of users and statistical data on their activity table with millions of millions of rows - high trafficked site about selling traffic = data grows incredibly. There is also a timestamp column in the user activity table. Would you advice to partition by year and subpartition by month based on the timestamp? Even better, I think by month and then by day could be better. Most checks are for today, yesterday, last 7 days, last 30 days, or this month or last month by timestamp. Any performance issue, if user wants to check this year stats by day or month? The details on visits sometimes get huge numbers and they seem to load slowly. Would partitioning the way I mentioned help? In every query there is a check where timestamp is between X and Y. Or maybe partition by user id? Based on what I know about database partitioning, I think by date would be best and may increase performance. To partition, I need to add the timestamp column to primary index. Maybe this alone would increase performance. I really ask because the table is huge and these operations will take lots of time, locks, waits... Maybe also, with partitioning, having MySQL check in the particular partition based on timestamp range, may further increase performance? Server version: 10.1.41-MariaDB-1~bionic - mariadb.org binary distribution. Would be best to only include one "future" partition? Any way to have it automatically create "future" partitions with a function like MONTH(timestamp) then for subpartition DAY(timestamp)? Sounds like super large stored procedures already. Just getting to know partitioning better. Thank you!

Upvotes: 0

Views: 359

Answers (1)

Rick James
Rick James

Reputation: 142433

Partitioning does not inherently provide performance.

There are a very few use cases where PARTITION helps. One is in DELETEing 'old' data based on a timestamp/datetime. But I don't hear you needing that?

Please provide the CREATE TABLE you have now, plus the main queries you need to run. Some indexing techniques may help. Or some Summary tables.

http://mysql.rjweb.org/doc.php/partitionmaint
http://mysql.rjweb.org/doc.php/index_cookbook_mysql
http://mysql.rjweb.org/doc.php/summarytables

Upvotes: 1

Related Questions