Article Hit Counter - Most Efficient Method

[XGCommander]

Hello all. I'm new to this forum and I was wondering if anyone has any input on a problem I am trying to solve. On my website we get a lot of articles on digg, reddit, and n4g which can seriously bog down our server. I have made a new version of our site that is still in testing. I have made a complete (and very efficient) caching system which eliminates ALL hits on the database when people view the site from the outside.

 

Our authors enjoy (and some features on the site require) seeing the number of hits an article has received. There are only two options (that I know of) to do this.

 

1. Hit the database every time someone views the page

2. Edit a local file every time someone views the page. It would update the database every time the article recaches or it would be on a cron job

 

My gut tells me that option 2 is more efficient on the server. If anyone can give me some input on which of these is better that would be most appreciated. If anyone knows a more efficient way of doing this then that would be even better.

 

Thank you in advance,

Commander

[schilly]

what would happen when more than one person tries to update the local file? would they be able to open it if someone else has it open in write mode already?

 

I'm not sure about concurrency when writing to file but it seems like this would be a problem.

 

The database on the other hand would handle this no prob and being that the page is cached, only the counter query would run which shouldn't tax you too much?

[XGCommander]

what would happen when more than one person tries to update the local file? would they be able to open it if someone else has it open in write mode already?

 

I'm not sure about concurrency when writing to file but it seems like this would be a problem.

 

The database on the other hand would handle this no prob and being that the page is cached, only the counter query would run which shouldn't tax you too much?

 

Yea that does sound like a problem with doing it file-based, though i'm sure there is a way to fix that. The problem is that if 1000 people hit the site in a minute (which is what happens for the 1st minute or two an article hits the front page of digg or reddit) it just totally kills the server with all those database calls. It isn't the query itself, it's firing the DBM.

[schilly]

found this if you want to try the file method:

 

http://stackoverflow.com/questions/293601/php-and-concurrent-file-access

 

Call it via ajax after the page loads then if it can't lock the file repeat after a second or so until it can write out the page view.

 

it might work.

[XGCommander]

yea that would work, but there is still the question of is efficient enough to be worth the trouble compared to directly writing to the db every time. Also, I REALLY feel like there has to be another way.

[schilly]

i dunno. i can't think of any other way to do it.

[XGCommander]

Is there a way to speed up queries like this?

[schilly]

isn't the query pretty basic already?

update articles set count = count+1 where article_id = $id

 

I'd say run an ajax call to a db query so it doesn't slow down the page load time for the user.

 

[XGCommander]

isn't the query pretty basic already?

update articles set count = count+1 where article_id = $id

 

I'd say run an ajax call to a db query so it doesn't slow down the page load time for the user.

 

There's a lot more to databases than queries. There are ways to cache queries, there are also things called stored procedures and stored routines.