14 March 2008
Views counter – MySIAM vs InnoDB
I’m working on a project where people have profile pages and we need to show a counter for how many views they’ve gotten. I saw two ways of doing this:
- Create an MySIAM table and everytime there is a view, insert a new row to represent the view. This could be nice because adding a timestamp would let you track views over time. Or stuff like IP address. To see the total, do COUNT(*) across a given user_id.
- Create an InnoDB table with a row for each user and a column that represents the count. On each view, do an update and increment the count. Then read that number out as the total. This seemed good because I wouldn’t have a single table with a HUGE amount of rows.
Google didn’t help me find any direction so I made a test. I created a table for each. In the case of the MySIAM I filled it with 10 million test records. Then I echoed out the total.
The result I found was that they basically take the same miniscule amount of time. Because I indexed the MySIAM on the user_id it optimizes really well and doesn’t seem to care that there are only 1000 views per user amongst the 10 million. Only how many views a user has. So a user with 10000 views takes longer to total than one with 100. I’m going to go with the MySIAM since it is storing richer data that may be useful down the road. Figuring I can switch to a InnoDB implementation later if I need to.
Comments
I kept messing with it and if a user has 100 views it was taking like .001 seconds. If they have closer to 100,000, it takes around .1 seconds to the count. That’s getting to scary territory. I’m gonna hedge on most users not being that popular.
What if you do both? On each profile view event, you could insert the new record into the MySIAM table and increment the count in the InnoDB table. Use the InnoDB for displaying totals and the MySIAM for tracking views over time.
It may cause bottlnecks on the ohtjer side, but it might be worth checking out.
So are you caching this information at all?
What if you did a daily cache and then only added the views from that day… or something similar? Then take the daily views and dump them into the cache when you start a new day.
Just a thought.
InnoDB is normally preferred over MyISAM. There are *some* benefits of still using MyISAM (full-text indexing for example), but overall performance is usually better with InnoDB.
I would create a DB table that has userID as a key and then a column for “page views” or whatever you want to call it. Then, any time some visits the page, a trivial query of: “UPDATE table SET pageViews=pageViews+1 WHERE userID=x” will give you an overall page view count.
Of course, this table scheme wouldn’t let you break-down stats like hourly/daily/weeky/monthly visit count. For something like that, you would definitely need to insert a new row with a timestamp and optionally an IP (to calculate unique visits) for each visit.