imgrooot Posted July 3, 2017 Share Posted July 3, 2017 I have a table like this. investment_id | investor_id | deposit | Date | Status I will be depositing funds into this table. For eg. If I am paying out 1000 investors at a time, 1000 rows will be added to this table. I might do this 100 times a day. That's 100,000 rows added in a day. Each deposit I do will be unique, even though it might be going to the same person multiple times. This could end up with million of rows. I am wondering if this is the correct way to do this or is there a better method? Quote Link to comment Share on other sites More sharing options...
Solution Jacques1 Posted July 3, 2017 Solution Share Posted July 3, 2017 If you have lots of data, there will be lots of rows. That's unavoidable. However, that doesn't mean there's a problem. Database systems were in fact made for handling large amounts of data, and “large” means billions of rows and terabyte-sized tables. There's a difference between the data you expect (or are hoping for) and the actual data. Experience shows that the number of users or the amount of activity tends to be exaggerated. Quote Link to comment Share on other sites More sharing options...
imgrooot Posted July 3, 2017 Author Share Posted July 3, 2017 (edited) If you have lots of data, there will be lots of rows. That's unavoidable. However, that doesn't mean there's a problem. Database systems were in fact made for handling large amounts of data, and “large” means billions of rows and terabyte-sized tables. There's a difference between the data you expect (or are hoping for) and the actual data. Experience shows that the number of users or the amount of activity tends to be exaggerated. I didn't know a database could hold billions of rows and still work fine. Good to know. That answers my question. Of course I was giving an extreme example when I said millions of rows. But if it can handle that, then great. I was a little hesitant before because I never had that many records stored in a table before. Edited July 3, 2017 by imgrooot Quote Link to comment Share on other sites More sharing options...
kicken Posted July 4, 2017 Share Posted July 4, 2017 I didn't know a database could hold billions of rows and still work fine. Good to know. That answers my question. Of course I was giving an extreme example when I said millions of rows. But if it can handle that, then great. I was a little hesitant before because I never had that many records stored in a table before. I had a little side project a few years ago downloading achievement data from the world of warcraft api to run some statistics on it. If I remember correctly my table linking characters to achievements and their completion dates got to about 8 million rows before mysql started to struggle with it. Adding partitioning let me get up to 12 million rows with mysql still handling it like a champ. This was on a fairly small VPS as well (20GB disk, 2GB RAM iirc). Ultimately I stopped around the 12 million mark not because mysql was struggling but because my VPS just didn't have enough disk space for the data and I didn't want to invest more money into it. Moral of the story is you probably shouldn't worry about how many rows you may be adding to a table. Focus on a proper database design and worry about the performance if you actually have a problem. Quote Link to comment Share on other sites More sharing options...
imgrooot Posted July 4, 2017 Author Share Posted July 4, 2017 I had a little side project a few years ago downloading achievement data from the world of warcraft api to run some statistics on it. If I remember correctly my table linking characters to achievements and their completion dates got to about 8 million rows before mysql started to struggle with it. Adding partitioning let me get up to 12 million rows with mysql still handling it like a champ. This was on a fairly small VPS as well (20GB disk, 2GB RAM iirc). Ultimately I stopped around the 12 million mark not because mysql was struggling but because my VPS just didn't have enough disk space for the data and I didn't want to invest more money into it. Moral of the story is you probably shouldn't worry about how many rows you may be adding to a table. Focus on a proper database design and worry about the performance if you actually have a problem. Good to know. Thanks for the info. I will keep it in mind. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.