[RESOLVED] [MySQL] Table structure (views)

[snapbackz]

Hi,

I have a table where I logged 'views' for an item that has a unique key on the user's IP and DATE (to only allow 1 view per day). This table logs a couple million rows each day and gets huge quickly. My solution for this was to create another table that simply UPDATE the 'total views'. This way I can clear the 'views' table each day and keep the size manageable. I don't know if this is the best solution but it's what I came up with.

I would like to expand this by displaying the top 'items' for each day, month, etc. The problem is that I don't want to run a COUNT() query to get this data.

Does anyone have experience with showing stats like this? I don't need any code, just the general idea on how this is efficiently done.

Thanks 🙂

[Sxooter]

Assuming you need to "roll up" these data each night at midnight, run the count(*) once then and put the results in a summary table.

[snapbackz]

Okay, had a feeling that was the way. Thanks Sxooter 🙂