Jump to content
Sign in to follow this  
imgrooot

Is there a better solution to storing this data in mysql table?

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

 

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 by imgrooot

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.