Hi there,
I've got a quick performance question for my real time news system. This is a community news system, where everybody can post his news, and vote for it, or vote for other news. Every 20secs in jQuery I search in the DB to refresh the 20 last news/votes.
But at the moment, I extract every 20sec the last 20 questions, even if nothing has been updated/post in the DB. Not very good performance speaking.
Now, I'd like to refresh the 20 last news (in order to add the new ones and update the votes for each ones) only if something has been posted or if someone have voted for a news.
I've got these 3 tables:
Group_news - id (AI), name => One line, define a specific group news
Posts - id (AI), id_group_news, poster, time (timestamp), nb_votes, ip => One insert per news
Votes - id (AI), id_post, time (timestamp), ip => One insert per vote
Now, I have two choices :
First One : Every 20sec, I search in the DB for new Posts AND Votes during the last 20sec (but I search through two large tables with lot of lines)
Second One : Every news post, every vote, I update Group_news and set the timestamp of the last action made in this group (but it means an update for each action, but search will be more efficient)
I tend to prefer the Second method, which one is really more effective (regarding the fact that I have 5-6news/min 3-10votes/min and between 20 and 600 users connected simultaneously refreshing every 20sec..)
Thanks a lot