1

I have a large user table in mysql with 50k+ entries. these users have limited download quota for their stored files in my website. currently the quota resets every 24 hours by resetting the traffic count in a seperate 'traffics' table (where each row has a userid and a trafficused field entry) so that makes two tables with 50k entries each (representing 50k users) this system is working fine so far, but I have no way of keeping a user's bandwidth usage history since to reset the quota per day, I have to clear the traffics table. I use php to update transferred bandwidth on each download completion. I need to be able to limit quota per day and/or per month as efficiently as possible without making a mess out of the mysql tables. There is another complication, I have seperate user quotas for different filetypes. for example .iso files have no quota, .mp4 files have 5gb per day limit. and .rar files have 10gb per day limit. and maximum user quota per day is 20gb.

I know it all sounds very confusing. I can post the table structures here if needed. Please try to help if you can. Thanks

O. Jones
  • 103,626
  • 17
  • 118
  • 172
user3847106
  • 101
  • 3
  • 11
  • Could just do a dump of that data before resetting it? But that isn't really the most effective way of handling it. – Darren Jul 17 '14 at 00:33
  • not really, thats an extra cron + data processing job. copying the data from one table to another every day does not make sense, does it? It will be a waste of resources. – user3847106 Jul 17 '14 at 00:50
  • That's why i stated `"But that isn't really the most effective way of handling it"`. Wouldn't be copying either, would be dumping into a `.sql` file. The only other alternative I can think of (*excuse the minimal thoughts, wayyyyyy to early here*) is to redesign your database schema to accommodate something that suites your needs but that doesn't look too viable in this case? – Darren Jul 17 '14 at 01:00
  • yes, I am willing to redesign the database. thats not the issue. – user3847106 Jul 17 '14 at 04:55
  • how did you solve this after 8 years? is your company still in business? million users now? @user3847106 – uberrebu Aug 25 '22 at 22:51

1 Answers1

1

50k rows is a medium-size table, not large. Don't fear that size, just index it correctly.

Try adding a DATE column to your traffics table. Once a day run a MySQL event to do this query: 

DELETE FROM traffics WHERE trafficdate < CURDATE() - INTERVAL 30 DAY

This will purge old traffic records.

When you need to know today's usage do

SELECT trafficused FROM traffics WHERE userid = whatever
                                   AND traFficdate = CURDATE()

Similarly, when you need 30 days' worth of traffic, do

  SELECT SUM(trafficused) FROM traffics WHERE userid = whatever

To store a traffic transaction, do this

  INSERT INTO traffics (userid, trafficdate, trafficused)
                VALUES (whatever, CURDATE(), filesize)
  ON DUPLICATE KEY UPDATE trafficused=trafficused+filesize

Make sure your traffics table has the composite primary key (userid,trafficdate).

Also create the compound index (userid,trafficdate,traffics) to make your queries faster.

This approach, with ON DUPLICATE KEY, means you don't have to have a row for every user for every day.

Handling separate bandwidth per file type is a question of adding a filetype column and putting it into the indexes and queries.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thank you for the reply, the purge system is already there. this query `INSERT INTO traffics (userid, trafficdate, trafficused) VALUES (whatever, CURDATE(), filesize) ON DUPLICATE KEY UPDATE trafficused=trafficused+filesize` will add a new row for every user for every day. so for 1 week, 50,000x7days rows. we cant have that :S – user3847106 Jul 17 '14 at 04:52
  • Are you saying every user uploads something every day? Seems unlikely to me. Are you saying MySQL cannot handle a 1.5 megarow (50k x 30 days) table? With respect, yes it can, easily. – O. Jones Jul 17 '14 at 11:25
  • but the quota is for downloads not uploads. yes the traffic usage is very high because we are a small cloud storage company. people upload and sync the files and movies with their tablets and phones etc. you are absolutely right, mysql can handle it fine im sure but whats the point of wasting resources when the same thing could be done more efficiently? 50k user count is just a rough estimate we are a new but growing service..user count is constantly increasing by 40 per day. this could be really bad in terms of performance in the long run. or are you sure its a good approach performance-wise? – user3847106 Jul 17 '14 at 22:39
  • and dont forget the fact that the quota is different for different filetypes because video encoding takes more resources. hence the table structure is something like: `traffics(userid, date, rar, mp4, pdf.....)` values (34, date, 34234234, 34343, 45454, 6454....)` now for this query : `SELECT SUM(trafficused) FROM traffics WHERE userid = whatever` things will get messy.. – user3847106 Jul 17 '14 at 22:40