Jump to content

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


imgrooot
Go to solution Solved by Jacques1,

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

  • Solution

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.

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.