InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

Question

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

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 clause (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.