1

In my application users can subscribe to other users, like in every social network out there. However I'm experiencing some trouble when a user has too many subscribers. Every time a user posts something all of his subscribers are notified via the notification system. When a user has like 2000 subscribers or even more, this means I have to insert 2000 rows into the notifications table, which may take a second or two. Having many users posting stuff I have the feeling that this table is going to get cluttered really fast and I can't think of another realization.

The current schema is

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `text` text NOT NULL,
  `data` text NOT NULL,
  `time` datetime NOT NULL,
  `seen` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

How can I overcome this problem?

  • 2
    Your described how your notifications are stored, but not how your notification system actually works. How and when are those notifications are processed? – Doc Brown Sep 28 '14 at 20:34
  • @DocBrown You seem to have missed it. I said that when users post stuff, simple updates on their profiles, all of their subscribers need to be notified about that. And so if a user has a large number of subscribers that means that I have to insert that many rows in the database and adding 5000 rows, for example, doesn't seem right. – php_nub_qq Sep 28 '14 at 20:39
  • Still repeating what you wrote does not clarify things. Does your program work, for example, this way: next time a user logs on to the system, the system checks if there are new notifications for him and presents the unsee ones to him? – Doc Brown Sep 28 '14 at 20:51
  • 1
    @php_nub_qq Well no, it is not straightforward. You could also have an email notification system that sends things immediately. In that case you have no need to even store this data in a table! – btilly Sep 29 '14 at 1:51
  • 1
    These people are trying to help. Maybe a less confrontational approach might be helpful. And you still didn't answer @DocBrown's question: how and when are those notifications processed? – Wilbert Sep 29 '14 at 12:45
3

Your use case is this (please correct me if this is wrong):

Users post updates. Users can have subscribers. Subscribers see the updates, and can filter based on which ones they have not seen before.

Your data model then should not contain a notifications table listing the notifications per recipient.

The data model would look like this:

  • Users (user_id, name): the list of users
  • Subscriptions (from_user_id, to_user_id): the subscriber/author relationship
  • Posts (post_id, user_id, text): the authored posts, one record per post (linked to the user who wrote the message)
  • Visits (post_id, user_id, date): the date the post was first visited by a specific user

The set of posts that a user has not yet seen is then the set of posts from authors they are subscribed to, subtracted with the set of posts found in the visits table.

Both sending and reading a notification is then a single-row update.

  • I bow before you sir. – php_nub_qq Sep 29 '14 at 12:44
5

Don't insert the 2000 rows when the user posts something.

Instead, send a message thru a a message que saying "New post, ID is x". Then have a background process take the message off the que and process it, including generating notifications.

Basically, you return a web page to the user ASAP by only doing the essentials and then do all the heavy work on a background task that the user doesn't see, and thus can take as long as it wants (with in reason).

  • That seems logical, but how does that work with mysql locking tables? For instance if I need to add many rows and tt takes about 5 seconds, would that mean that SELECT queries will have to wait for INSERT to finish? – php_nub_qq Sep 29 '14 at 10:11
  • This answer does not differ essentially from what I wrote already 12 hours before. – Doc Brown Sep 29 '14 at 11:53
3

For a system which can handle >2000 users, adding 2000 records to a table should not be much of a problem, otherwise you have chosen the wrong hardware. If the one or two seconds block your main process for too long, add the records asynchrounously in a separate thread or process.

And if you have concerns about getting too many notification records in that table, think about how long those notifications really need to be stored. Will they really have to be stored once the notification was seen? Do they have to be stored for more than, for example, 60 days? These thoughts should help you finding an appropriate clean-up strategy.

0

You can able to greatly increase the speed by putting your inserts inside a transaction. You can also move your prepare and bind statements outside, if you need so.

The idea I generally use when working with transactions looks like this (semi-pseudo-code):

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
}

Example taken from this link:- https://stackoverflow.com/questions/2708237/php-mysql-transactions-examples

  • How do transactions speed up queries? I thought they did exactly the opposite? – php_nub_qq Sep 29 '14 at 10:13
  • If you don't explicitly use a transaction, then MySQL will still basically run a transaction for you for each separate statement. So the choice is - run 100 inserts, with 100 transactions, or run a transaction yourself and run 100 inserts and 1 transaction which is faster. I think anyway; I'm not sure of the theory but I've tested and it definitely helps. – James Sep 30 '14 at 17:42
  • Yes @James. You are right, Transaction basically increases speed, "-php_nub_qq-" you have add all you inserts statements manually within one transaction.. I was offline for few days, so not able to rply – Vizllx Oct 20 '14 at 7:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.