mysqldenormalization

Should totals be denormalized?


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?


Solution

  •    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).