[AGISB]

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

[r937]

you have no indexes

[AGISB]

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.

[AGISB]

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