Tracking Hits

I have three items which I would like to track the number of hits on. Those three items are projects,blogs and threads.

The tables pertaining to those individual items are below.

Code SQL:

---

CREATE TABLE threads (
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
	,user_id INT(10) UNSIGNED NOT NULL
	,topic_id tinyint UNSIGNED NOT NULL
	,title VARCHAR(40) NOT NULL
	,message text NOT NULL
	,STATUS tinyint UNSIGNED NOT NULL
	,created TIMESTAMP NULL DEFAULT NULL
	,PRIMARY KEY(id)
)ENGINE=MYSIAM;
 
CREATE TABLE projects (
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
	,user_id INT(10) UNSIGNED NOT NULL
	,category_id tinyint UNSIGNED NOT NULL
	,range_id tinyint UNSIGNED NOT NULL
	,title VARCHAR(40) NOT NULL
	,message text NOT NULL
	,STATUS tinyint UNSIGNED NOT NULL
	,created TIMESTAMP NULL DEFAULT NULL
	,PRIMARY KEY(id)
)ENGINE=MYSIAM;
 
CREATE TABLE blog_entries (
	id mediumint UNSIGNED NOT NULL AUTO_INCREMENT
	,user_id INT(10) UNSIGNED NOT NULL
	,title VARCHAR(40) NOT NULL
	,entry text NOT NULL
	,picture VARCHAR(100) NOT NULL
	,created TIMESTAMP NULL DEFAULT NULL
	,PRIMARY KEY(id)
)ENGINE=MYSIAM;

---

Would the best way to track the hits be to add a field named hits to each of these tables? Then update that value each time the entity is hit via AJAX?

PHP Code:

---

$project = new Project(3);
$project->hits+=1;
$project->save(); 


---

That seems like the most straightforward and simplest solution to this problem anyways.

Would this be the "best" way to do this or is there an alternative approach that you might recommend?

thanks

I like it, but it's not the greatest idea from a performance perspective if you expect high traffic.

You mean the method of just adding a hits field and incrementing it via AJAX after onload event fires for the window?

What would be the more performance oriented approach if you don't mind me asking?

I'm no expert here...but

For one, its an extra http request, which always adds load. It's probably pretty possible for you to just do the increment when you generate the page. But, if you're caching in a way which doesn't even hit php for a page view, then I really see no other easy choice.

I beleive mysiam locks the entire table when you write, and keeping track of hits makes me think you're gonna have a ton of writes(which will only occur one at a time, they get queued in order). A write locked mysiam table can't be read. Now, I don't want to blow that out of proportion, because these write will probably be pretty fast, espescially if you don't maintain any indexes on the hits column. I'm not familiar with how mysql manages writer/reader starvation, but it just seems like something that probably won't fare great under high load.

innodb uses row level locking, which would probably improve this specific performance aspect so long as traffic isn't focused on specific rows(ala slashdot effect to your latest blog post).

You could also maybe use a seperate table to store hits, and occasionally aggregate that data into those 3 tables. That would keep the other tables available for reading much more often if a delayed hit count is acceptable.

I'm probably being way too extreme...

So something like this might be a overkill?

Code SQL:

---

CREATE TABLE thread_hits (
 
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
	,thread_id INT(10) UNSIGNED NOT NULL
	,hits INT(10) UNSIGNED DEFAULT 0
	,PRIMARY KEY(id)
	,UNIQUE KEY(thread_id)
 
) ENGINE=MYSIAM;
 
CREATE TABLE project_hits (
 
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
	,project_id INT(10) UNSIGNED NOT NULL
	,hits INT(10) UNSIGNED DEFAULT 0
	,PRIMARY KEY(id)
	,UNIQUE KEY(project_id)
 
) ENGINE=MYSIAM;
 
CREATE TABLE blog_entry_hits (
 
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
	,blog_entry_id INT(10) UNSIGNED NOT NULL
	,hits INT(10) UNSIGNED DEFAULT 0
	,PRIMARY KEY(id)
	,UNIQUE KEY(blog_entry_id)
 
) ENGINE=MYSIAM;

---

I really don't know if it's overkill or not. I'm just more trying to throw out ideas to be thought about.

I don't really see the need for the auto inc id column.

Since these new tables are write heavy/read light, I think innodb would shine here. But I'm assuming these new tables are only read occasionally, and thier values used to do the occasional update of the other three tables. Doing a join on these tables everytime you read the orig tables would defeat the purpose, and probably perform worse.

I'm not sure of your circumstances or goals, but if its just for your own personal use, why not just use something like Google Analytics?

crmalibu already gave a good warning about the possible performance drawbacks of using the sql approach, but in my opinion its most likely negligible.

Storing the hits in a separate table may be a viable solution, but I'd imagine you're going to accumulate lots of rows quickly. Whether or not thats ok is up to you.

Well there would be one row per entity. A new row wouldn't be created for each hit on the item.

Not sure if GA will handle Ajax though, mighty interested if it does.

I read that this is the kind of job that sqlite was designed for.

"insert into all_hits (resource_type, id_ref ) values (1, 3 )"; (1=forum, 2=thread etc)

Considering that the hits will be accessed on a regular basis is it probably best to store them inside the main tables for blog entries, projects and threads? I'm not really sure the best way to approach this given the hits will be displayed on the listing pages for each one of these items. In that instance a separate table is just more overhead. Each solution presented here was its obvious advantages and disadvantages considering these table engine types are MYISAM for full text searching.

I am glad you brought this thread back to life.

Your comments, and my sqlite comment above, made me think a bit more seriously about how to tackle logging on my next project. I want to capture all kinds of behaviour info, and also trap errors and exception data at various levels.

I found out about PEAR::Log and that seems to do the biz - and had an sqlite handler too.

Not sure if PEAR::Log will help you, but its pretty simple to just make 3 tables in sqlite. Each table is also just a file, and can be rotated, moved around, deleted etc. Anyhow your thread helped me.

Thanks.

(The only problem was that Log didn't have an sqlite pdo handler, so I made one)