Optimal design for database scenario [duplicate]

74
March 29, 2019, at 6:40 PM

This question already has an answer here:

  • How many rows in a database are TOO MANY? 10 answers

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.

Answer 1

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.

READ ALSO
Error (1142) SELECT command denied on session_variables table with mysqldump

Error (1142) SELECT command denied on session_variables table with mysqldump

When I try to run mysqldump -u dump -p myschema > dumpfilesql I get this error message:

92
Practical implementation of multi-tenant approach in mysql / mariadb (or amazon aurora)

Practical implementation of multi-tenant approach in mysql / mariadb (or amazon aurora)

I fully understand the pros and cons of using shared vs separate schemas (or databases in mysql)Looking at those we have chosen to use a shared schema

59
Group by and order by range of age in MySQL

Group by and order by range of age in MySQL

I have this query that display the age range and the total without problem:

130