Search code examples
phpjavascriptsqlmessage

Unread message count in a PHP app

I am currently developing a simple PHP application where users can send messages to each other. Messages are stored in a SQL database. I'd like to put a count of unread messages in the menu on every page, so that a user can see quickly if they have new messages without checking the inbox periodically.

While this may be an easy problem to solve, I don't know what the best method would be, performance-wise :

  1. Do a plain SQL COUNT() of unread messages on every page load (instantly notified of changes, but it may impact performance badly ?)
  2. Do the same thing, but cache the result for X minutes (we create an annoying delay)
  3. Same as 2., but only update when we read a message or when a message is sent to us (can use up a lot of RAM / stress the disk, since we create one persistent entry/file per user : we can't store it in $_SESSION because we need to update it when another user sends a message to us)

All my solutions are somewhat server-based, because I'm not very familiar with JS. But if a better solution exists using JavaScript, It's okay.

Thank you for your help !

Solution

  • I'd suggest 4'th:

    Once new message has been sent to a user, you update counter in memcache. You create simple ajax application on client side sending a request every X seconds. At server side, you just check is there unread messages. At page refresh, you don't need to query the database since you get count from memcache extremely fast.

    That's what I'd done if I had bottleneck in DB (in 90% cases, DB is the weekest part of any database-driven application).

    That's what we usually do at highloaded web sites: we trying to avoid any COUNTER queries. If not, we denormalize the database to store counters right in the appropriate table as yet another column e.g. if you can not use memcache, you would store the unread messages counter as a column for Users table.