Results 1 to 4 of 4

Thread: Forum Design

  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Forum Design

    I am designing a database for a small forum. Originally I had a forum table a threat table and a post table. I now have a forum table and a post table as threats can be recognized by a post that has no answers. So my Post table structure is:

    `post_id` int(10) unsigned NOT NULL auto_increment,
    `thread_id` int(10) unsigned default NULL,
    `forum_id` int(11) NOT NULL,
    `userid` int(10) unsigned default NULL,
    `parentPost` int(11) default NULL,
    `post_title` varchar(250) character set latin1 default NULL,
    `post_text` text character set latin1,
    `post_creation` datetime default NULL,
    `isPoll` tinyint(1) NOT NULL default '0',
    `pic` varchar(32) collate latin1_german1_ci default NULL,
    `thread_flag` enum('0','1') collate latin1_german1_ci NOT NULL default '0' COMMENT '0=normal, 1=sticky
    `isOpen` tinyint(1) NOT NULL default '1' COMMENT '0=closed for writing, 1 open for writing',
    `post_status` time NOT NULL
    `IP` varchar(15) collate latin1_german1_ci NOT NULL,
    `hits` int(11) default NULL,
    PRIMARY KEY (`post_id`)

    Now I am have a problem with getting the threats with a good performance. I got following:

    SELECT a .* ,
    (SELECT max( b.post_creation ) AS maxi FROM posts AS b WHERE a.post_id = b.post_id OR a.post_id = b.parentPost AND b.post_status = '0' ) AS wert
    FROM posts a
    WHERE a.parentPost IS NULL
    AND a.forum_id = '--id-here--'
    ORDER BY thread_flag DESC , wert DESC

    I made 800 threats and the performace is at about 1.07 Secs for the query which is way to long but I am stuck thinking of another way. Do I really have to get the max for each threat seperately and resort the enties in an array?

    Maybe a better design is necessary ...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have no indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    Thanks, unfortunately it didn't help as I have only threats in the post table to check my navigation when I stumbled over the performance.

  4. #4
    Join Date
    Mar 2004
    Posts
    15
    It might just be a good idea to update the threat post entry with the last time stamp every time I post to that threat. Simple update on posting but a simple select were it cost performance ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •