Counting / Calculating "Views"

I have a number of pages and resources that I want to calculate the views of. As of right now I store the page/resource ID, IP address, Username (if available) and the date.


Though this makes the database huge, so I then compress each daily count into a new table. So then each resource has the overall count for that day. This is also getting huge.


The problem is, when I go to retrieve the resource/page and want to display the counts, it can take some time.


Though, the code that people on here have helped me with is pretty efficient, but I have a problem customizing it. I tired doing it myself though ran into a problem that made my hosting account get suspended.


Either way here is the code:

SELECT
  u.id, u.title, 
  u.genre, u.content, 
  u.url, u.approved, 
  COALESCE(v.views, '0') + COALESCE(vc.old_views, '0') AS views, 
  r.likes, r.dislikes 
 FROM 
    ( SELECT * 
      FROM uploadsNew 
      WHERE type = '1' AND status = '0'
      [B]ORDER BY approved DESC
      LIMIT 0, 22[/B]
    ) AS u 
  LEFT JOIN 
    ( SELECT 
      id, COUNT(*) AS views 
      FROM views 
      WHERE type = '0' AND subtype = '1' 
      GROUP BY id 
    ) AS v ON v.id = u.id 
  LEFT JOIN 
    ( SELECT 
      id, SUM(views) AS old_views 
      FROM viewsCondensed 
      WHERE type = '0' AND subtype = '1'
      GROUP BY id 
    ) AS vc ON vc.id = u.id 
  LEFT JOIN 
    ( SELECT 
      upload, 
      SUM(rating = '1') AS likes, 
      SUM(rating = '-1') AS dislikes, 
      IF(username = '', rating, '0') AS user_rated 
      FROM ratingNew WHERE type = '1 ' 
      GROUP BY upload 
    ) AS r ON r.upload = u.id 
  [B]ORDER BY u.approved DESC[/B]

Code (markup):

Now the bolded order parts i the area where I am having trouble. I want to have 4 different options for the user: view newest, popular, most viewed, highest rated.


They go like this:
newest: ORDER BY approved DESC
popular: ORDER BY views DESC (though this limits the views to the current week)
most viewed: ORDER BY views DESC (all views ever)
highest rated: ORDER BY rating DESC


The problem is, if I were to get rid of the ORDER BY approved DESC LIMIT 0, 22 within the SELECT of the uploadsNew, it maeks it drastically slower if I put it at the bottom like it is. This causes problems and doesn't get back the correct things that I actually want, since the second ORDER BY only works with the 0 - 22 rows that got back from the uploadsNew.


I hope this makes sense. Can anyone help me out as to how to successfully do my idea, or at least how do other big companies calculate and store these "views" into the database?

 

Few suggestions:

1. I noticed that type, subtype, status and rating are queried with quotes around values. Either they are stored in varchar or queried that way. Both are wrong. Store numeric data into one of numeric datatype and query them without quotes. Increases speed to great extent for large data.
2. Might you wanna split huge queries into small / multiple queries probably with limit to them to reduce the amount of data being played with.
3. I presume that entries in tables are made when an event from UI occurs, mostly click done by visitor or on page load. You could on such time update a summary table along with insert in above tables, thus display of such data becomes very simple query. Simply read from a linear table with above columns based on id.

Above are suggestions to implement best practices within / outside mysql.