CODEWITHSUNDEEP
mysql
user15063
user15063

Reputation:

Keeping track of views for content in mysql

Im wondering if I should bother changing my system.

Currently when a video is viewed, it runs a query that does 

UPDATE table SET hits=hits+1 WHERE id = $id.

very simple, except they appear in the slow query log all the time, sometimes with 3-4 seconds of query_time.

What if I INSERT every view as a new row into a memory table, and commit changes to DB with cron every hour? Will there be a significant difference in performance? Are there any alternatives?

Upvotes: 0

Views: 104

Answers (2)

timdev
timdev

Reputation: 62884

I would probably approach it the way you're thinking about. Log views to a simple table optimized for writes, and periodically update the view count and purge the log table.

With the sort of traffic you're seeing, you may find that such a system will work well if you run your cron more than once an hour.

As Mark says, though, you probably have something else going on that should be figured out.

Upvotes: 0

mmmmmm
mmmmmm

Reputation: 32661

You might want to check you have an index on id as that update should not take many seconds. This is about the simplest update you can do so you can't really speed it up.

If it does I think you have other problems with your database.

Upvotes: 1

Related Questions