Efficient way of handling large number of data in MySQL

Question

I have a scenario and two options to achieve it. Which one will be more efficient?

I am using mySQL to store attendance of students (around 100 million). And later use this attendance data to plot charts and results based on user’s selection.

Approach.1) Store attendance data of student for each day in new row (which will increase the number of rows exponentially and reduce processing time)

Approach.2) Store serialized or JSON formatted row of one year’s attendance data of each student in a row (Which will increase processing time when updating attendance each day and reduce database size)

Answer 1

First I think you are confused, the number of rows will increase linear not exponential that is a big difference.

Second 100k is nothing for a database. even if you store 365 days that is only 36 million, I have that in a week,

Third Store in a JSON may complicated future query.

So I suggest go with approach 1

Using proper Index, design and a fast HDD a db can handle billion of records.

Also you may consider save historic data in a different schema so current data is a little faster, but that is just a minor tuneup