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

[imgrooot]

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?

[Jacques1]

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.

[imgrooot]

 

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

[kicken]

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.

[imgrooot]

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.