Should totals be denormalized?

Question

I am working on a website with a simple normalized database.

There is a table called Pages and a table called Views. Each time a Page is viewed, a unique record of that View is recorded in the Views table.

When displaying a Page on the site, I use a simple MySQL COUNT() to total up the number of Views for display.

Database design seems fine, except for this problem: I am at a loss for how to retrieve the top 10 most viewed pages among thousands.

Should I denormalize the Pages table by adding a Pages.views column to hold the total number of views for each page? Or is there an efficient way to query for the top 10 most viewed pages?

Answer 1

   SELECT p.pageid, count(*) as viewcount FROM 
   pages p
   inner join views v on p.pageid = v.pageid
   group by p.pageid
   order by count(*) desc   
   LIMIT 10 OFFSET 0;

I can't test this, but something along those lines. I would not store the value unless I have to due to performance constraints (I just learned the term "premature optimization", and it seems to apply if you do).

Answer 2

It depends on the level of information you are trying to maintain. If you want to record who viewed when? Then the separate table is fine. Otherwise, a column for Views is the way to go. Also If you keep a separate column, you'll find that the table will be locked more often since each page view will try to update the column for its corresponding row.

Select pageid, Count(*) as countCol from Views
group by pageid order by countCol DESC
LIMIT 10 OFFSET 0;