2

I have a ten minute long video where people can stop the video and leave comments. The time (in seconds) and the comment are both saved in the database. There can be multiple comments left at the same second.

When there is a comment available to be viewed, an icon is displayed. I am using a 10 second window so a comment left at 8 seconds will be available from 3 - 13 seconds.

I am currently polling the database every 2 seconds with

SELECT COUNT(*) AS count FROM comments WHERE time BETWEEN $time-5 AND $time+5

When somebody clicks the icon to view the comments, I retrieve the relevant comments from the database.

Would it be better - performance wise - to get all the comments at the beginning, build some sort of javascript object or array, and iterate over the objects with a custom between method?

I know it depends on how many comments I am working with but I don't want to go through the hassle of building the javascript solution and running performance tests if the MySQL COUNT queries are inconsequential.

Improve this question

3 Answers 3

Reset to default
3

That MySql query will be very quick, but remember that in general server requests are much slower than doing everything on the client side. I would store all comments in a JavaScript array in the first place (as you suggest), and then poll the array. This keeps all the processing on the client. It will be particularly useful in the event that your server is, for any reason, being slow that day.

EDIT: In response to Nick's valid comment, I suppose the answer depends on how many comments there are. If there are loads (thousands?) and you expect new comments to be posted every few minutes, it's better to keep polling the server.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This does come with the downside that the array is stale as the user loads the page. Also, depending on the number of comments, it might have been a waste to cache them all at once when the user only watched the first 10s of video. (Not to mention it might slow down the video buffering.)
Perhaps a more scalable solution would be to load the number of comments per second into the JavaScript array in say 70s blocks, then load from that, and refresh this list from the server each time the user manually moves the position in the video back or forwards, or when it has been 60s since the list was last refreshed, based on the current position of the video (t-5s and t+65s).
1

I'd select just the id+timestamp from comments and then either load the comments themselves on user action (click, hover) or simply load comments as movie progresses. It's better than querying the database every second/two seconds, because HTTP requests are generally rather slow compared to in-memory computation. I think it's also better than loading comments all at once, because loading comment data would likely slow down the initial request and probably most of the time you don't even need those comments anyway. The downside is that the user will never see the comments that were added after the initial load, but I think it's a rather small issue.

Improve this answer

Comments

1

I would have thought that would be more based on the number of hits the site gets - the more page hits, the more of these counts you are going to be running.

Sure if they are indexed properly you'll only be hitting the index, but building something to query the db once and prepare a javascript array containing (for example) one array element for each second within the video, and then populating the array value for each with the number of relevant comments would be more efficient if you get a large number of hits on the site...

Just my tuppence-worth...

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.