Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them?

Posts and Comments table design below

CREATE TABLE `my_posts` (
  `forum_id` mediumint(8) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,          // not an auto increment
   subject   varchar(200) NOT NULL,
  `details` text NOT NULL,
  `access` tinyint(3) unsigned NOT NULL,  //private,friends,public
  `created_by` int(10) unsigned NOT NULL,
  `created_on` int(10) unsigned NOT NULL,
  `updated_on` int(10) unsigned NOT NULL,
  `comment_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `ip_address` int(10) unsigned NOT NULL,
   sphinx_unique_id  int(10) unsigned NOT NULL,  // not an auto increment
   PRIMARY KEY (`forum_id`,`post_id`,created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `my_posts_comments` (
  `forum_id` mediumint(8) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,          // not an auto increment 
  `comment_id` int(10) unsigned NOT NULL,
  `details` text NOT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `created_on` int(10) unsigned NOT NULL,
  `ip_address` int(10) unsigned NOT NULL,
  PRIMARY KEY (`forum_id`,`post_id`,comment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have queries based on forum_id, post_id, created_by and also sorting on updated_on , comment_count

SELECT * FROM my_posts  WHERE access=public ORDER BY updated_on DESC      //OR ASC
SELECT * FROM my_posts  WHERE access=public ORDER BY comment_count DESC      //OR ASC
SELECT * FROM my_posts WHERE forum_id=?  and access=public ORDER BY updated_on DESC      //OR ASC
SELECT * FROM my_posts WHERE forum_id=?  and access=public  ORDER BY comment_count DESC   //OR ASC
SELECT * FROM my_posts WHERE created_by=?  ORDER BY updated_on DESC     //OR ASC
SELECT * FROM my_posts  WHERE created_by=(Friends_ids) AND access=(public,friends)   ORDER BY updated_on DESC      //OR ASC 
SELECT * FROM my_posts WHERE forum_id=? AND  post_id=?  
SELECT * FROM my_posts_comments WHERE forum_id=? AND post_id=?

1) Since the composite primary key on forum_id, post_id, created_by , I see problem accessing it just conditioning on created_by . What is the best design to query on created_by? Can I remove created_by from composite key and just add index for it . I will loose Cluster performance here ..

2) What is the best way to increment post_id on forum level ? Or can I increment at table level . for example

sample data ( Forum Level )

 FORUM_ID  POST_ID
    1         1
    1         2
    1         3
    1         4
    2         1
    2         2
    2         3
    2         4
    2         5

Or table level

 FORUM_ID  POST_ID
1          1
1          2
1          3
1          4
2          5
2          6
2          7
2          8
2          9

If I can use the above table level increment , I can remove sphinx_unique_id column since I am using it only for sphinx search . Also in comments table , I can remove forum_id column .

3) All the queries I have sorting on updated_on and on comment_count . These two are not of PK , so the first two queries looks like a major problem ..

Thanks all for your help

share|improve this question
1  
The "table level" incrementing is easy. Just make post_id auto-incrementing (and add a Unique constraint on it.) –  ypercube Jul 5 '13 at 13:17
    
Thank You ypercube for your response . Hope auto incrementation ( table level) on Composite key won't create any fragments or any other problems at physical level . –  Bujji Jul 5 '13 at 13:25
1  
The auto increment will not cause fragmentation. The order of columns in the Primary Key should be a concern though and under consideration/test. You have various choices for the clustering (PK) and testing will reveal any problems. –  ypercube Jul 5 '13 at 13:28
    
Thanks again ypercube –  Bujji Jul 5 '13 at 13:38
1  
The index will be equivalent to (post_id, forum_id, created_by). The post_id will not be duplicated. –  ypercube Jul 5 '13 at 15:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.