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?