Jump to content

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


imgrooot

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?

Link to comment
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.
Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.