i just have 2 points to mention for this subject: 1) If you are going to use an RDBMS to store the messages using MySQL then use `InnoDB` as your table engine instead of `MyISAM` 2) This kind of tables by nature grows faster than the most of the other tables in terms of records in your system, and hence you will reach a scalability and performance issues once this table goes for large amount of data, hence i would recommend considering IMAP for such a task.
i have worked on such an environments before with very high traffic and very large amount of data and after pulling our hairs off to boost the performance we saw that IMAP was the best solution ever for this kind of tasks Ma'moon On Wed, Feb 23, 2011 at 8:08 PM, euromark <dereurom...@googlemail.com>wrote: > sure > > CREATE TABLE IF NOT EXISTS `comm_conversations` ( > `id` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', > `user_id` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', > `title` varchar(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', > `created` datetime NOT NULL, > `last_message_id` int(10) unsigned NOT NULL DEFAULT '0', > `allow_add` tinyint(1) unsigned NOT NULL DEFAULT '0', > `count` int(10) unsigned NOT NULL DEFAULT '0', > PRIMARY KEY (`id`), > KEY `user_id` (`user_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > CREATE TABLE IF NOT EXISTS `comm_conversation_messages` ( > `id` int(10) unsigned NOT NULL AUTO_INCREMENT, > `conversation_id` char(36) COLLATE utf8_unicode_ci NOT NULL, > `user_id` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', > `attachment_id` int(10) unsigned NOT NULL DEFAULT '0', > `message` text COLLATE utf8_unicode_ci NOT NULL, > `created` datetime NOT NULL, > PRIMARY KEY (`id`), > KEY `user_id` (`user_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci > AUTO_INCREMENT=109 ; > > CREATE TABLE IF NOT EXISTS `comm_conversation_users` ( > `id` int(10) unsigned NOT NULL AUTO_INCREMENT, > `conversation_id` char(36) COLLATE utf8_unicode_ci NOT NULL, > `user_id` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', > `status` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '0=ok, > 1=deleted,2=removed', > `last_view` int(10) unsigned NOT NULL DEFAULT '0', > `created` datetime NOT NULL, > PRIMARY KEY (`id`), > KEY `user_id` (`user_id`), > KEY `conversation_id` (`conversation_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci > AUTO_INCREMENT=110 ; > > > > > > On 23 Feb., 21:31, Josh <josha...@gmail.com> wrote: > > Hey euromark, > > > > That looks like exactly what I'm trying to do. Could you also let us > know > > what columns are in each table? I'm sort of following your logic, but > not > > 100% and I think seeing the table columns would help a lot. > > > > ~Josh > > -- > Our newest site for the community: CakePHP Video Tutorials > http://tv.cakephp.org > Check out the new CakePHP Questions site http://ask.cakephp.org and help > others with their CakePHP related questions. > > > To unsubscribe from this group, send email to > cake-php+unsubscr...@googlegroups.com For more options, visit this group > at http://groups.google.com/group/cake-php > -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
