-2

I am building a system that keeps track of the visits of members to some clubs.

As I see it, I have 2 options to keep track of the visits, just insert one row into the visits table for each visit and when I need the total, I can just select count, when I need to display i can just do a simple select.

The problem, this is going to grow fast and I am sure I will have eventually like millions of rows just in this table.

Can mysql handle this with ease? Or better implement the second option, one row for each member, and store in one of the row cells the total amount of visits and in another cell the last 60 visits (not really more needed).

I guess the answer as to what's better is obvious but I am curious about how much mysql can handle because the previous system implemented 1 row for each visit.

Max
  • 1
  • 3
  • Less rows also mean less information. For example, if you want to analyse trend data (visits over time), then you have to store a record per visit. – Shadow Mar 28 '19 at 20:52
  • Millions of rows? Yawn. Billions? Now it gets interesting -- but not impossible. – Rick James Apr 19 '19 at 05:52

1 Answers1

0

One row per visit sounds fine. You could also store a date/time for each visit, so you would have a better history. As long as you index the id column in the visits table, things will be speedy when you do something like this:

SELECT COUNT(*) FROM Visits WHERE MemberID = '1'

MySQL can handle millions of rows. Our current system stores hundreds of records per user per day and we have hundreds of thousands of users. You could always roll off old records if you wanted to.

You could also use table partitioning and partition by year, club, etc.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • Thank you, I guess my question was kind of dumb but I now I now know the strength of MYSQL. – Max Mar 31 '19 at 03:37