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
Best Answer
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.