Jump to content


Photo

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

mysql storing data investment solution

Best Answer Jacques1, 03 July 2017 - 08:17 PM

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.
Go to the full post


  • Please log in to reply
4 replies to this topic

#1 imgrooot

imgrooot
  • Members
  • PipPipPip
  • Advanced Member
  • 210 posts

Posted 03 July 2017 - 07:41 PM

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?



#2 Jacques1

Jacques1
  • Members
  • PipPipPip
  • Turtles all the way down
  • 4,224 posts

Posted 03 July 2017 - 08:17 PM   Best Answer

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.


#3 imgrooot

imgrooot
  • Members
  • PipPipPip
  • Advanced Member
  • 210 posts

Posted 03 July 2017 - 08:25 PM

 

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, 03 July 2017 - 08:25 PM.


#4 kicken

kicken
  • Gurus
  • Wiser? Not exactly.
  • 3,343 posts
  • LocationBonita, FL

Posted 04 July 2017 - 02:31 AM

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.
Did I help you out? Feeling generous? I accept tips via Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7
Kicken's World⦄ ⦃Recycle old CD's

#5 imgrooot

imgrooot
  • Members
  • PipPipPip
  • Advanced Member
  • 210 posts

Posted 04 July 2017 - 08:31 PM

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users