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

1 Answer 1

You can and will be better served by removing created_by from your primary key and creating a separate index for it. This will allow those queries which use created_by in the where e.g. where created_by = ?, to be able to use that index to service the query. There may be a slight, and probably not noticeable, performance hit from managing the primary key and the index on an insert. However this price is nothing to pay for not doing full table scans when executing such queries needing it to be be the primary search in the where clause.

share|improve this answer

Your Answer

 
discard

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

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