在MySql中存储每小时更新的数据

Question

There are few thousand product listings on the site.

The goal is to store updated listing information for each product (views/likes/etc) in MySql Database ever 15 mins or so while keeping previous results as well.

This data will be used to show charts with the ability to display hourly, daily, weekly and so on stats.

The question is, what is the most efficient way to store this data in mysql table?

There are two approaches that I can think of:

1) Store each 15-min update as a separate record and then retrieve a data range when needed. The problem with this is that this table can quickly escalate into multi-million table which doesn't seem that efficient at all.

2) For each 15-min update append required data (views/likes/etc) to the end of existing data string in corresponding columns, and then on retrieval explode that string into array. Since there would be no ranges I could store last updates data stamp and calculate ranges from there. The problem with this it could be less reliable and data strings will grow big overtime as well.

Thanks in advance!

Answer 1

Solution one is by far the better answer. Don't worry about how many rows are created. For some reason people get obsessed over how many rows tables have. The latter answer you will be violating database normalization by storing multiple values in a single record.