php - Storing hourly updated data in MySql


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!




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.

People are also looking for solutions to the problem: php - Define property zerofill and size on field schema migration with laravel


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.