Ok, well I have one table, called `Forum`.
[sql]CREATE TABLE IF NOT EXISTS `Forum` ( `ID` int(11) NOT NULL AUTO_INCREMENT, #self explanatory `Type` varchar(30) NOT NULL, #post type - either post, thread, or forum `Forum` int(11) NOT NULL, #forum id `Thread` int(11) NOT NULL, #thread id `Subject` varchar(75) NOT NULL, #self explanatory `Post` longtext NOT NULL, #self explanatory `Author` varbinary(15) NOT NULL, #self explanatory `Date` int(11) NOT NULL, #self explanatory `Edit` varchar(30) NOT NULL, #last edit timestamp `Stats` int(11) NOT NULL, #view counter `Status` varchar(255) NOT NULL, #permissions and so on `Mod` varchar(255) NOT NULL, #moderators `Options` int(11) NOT NULL, #an options bitfield, for parsing PRIMARY KEY (`ID`), KEY `Type` (`Type`), KEY `Forum` (`Forum`), KEY `Thread` (`Thread`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2856 ;[/sql]
And here's my attempt at commenting the query:
[sql]SELECT`F`.*, #selecting all thread fieldsCOUNT(`P`.`ID`) AS `PostCount`, #the number of posts the thread hasIF(`F`.`Type`='forum', #if it's a forum, don't select tracking stats with a thread id, and sum them up, otherwise select the thread's(SELECT SUM(`D`.`PostCount`) FROM `Forum_Data` `D` WHERE `D`.`Alias`='jackpf' AND `F`.`ID`=`D`.`Forum`), (SELECT `D`.`PostCount` FROM `Forum_Data` `D` WHERE `D`.`Alias`='jackpf' AND `F`.`Forum`=`D`.`Forum` AND `F`.`ID`=`D`.`Thread`)) AS `Forum_Data_PostCount`,IF(`F`.`Type`='forum', #if it's a forum, sum up its threads' stats. otherwise, just its statsSUM(`P`.`Stats`), `F`.`Stats`) AS `Stats`,MAX(`P`.`ID`) AS `LastPost` FROM `Forum` `F` #the thread's last post's id INNER JOIN `Forum` `P` ON IF(`F`.`Type`='forum', `F`.`ID`=`P`.`Forum` /*OR `F`.`ID`!=`P`.`Forum`*/, `F`.`ID`=`P`.`Thread`/*)*/ AND `P`.`Type` IN('thread', 'post')) #this just joins the forum's/thread's posts on, for stats and stuff WHERE `F`.`Forum`='2009' OR SUBSTRING_INDEX(`F`.`Status`, ':', -1)=2 #the first condition selects only threads in this particular forum, the second selects all "announcements" GROUP BY `F`.`ID` #... ORDER BY IF(`F`.`Type`='forum', 0, 1), IF(SUBSTRING_INDEX(`F`.`Status`, ':', -1)>=1, 1, 2), `LastPost` DESC; #orders child forums, then stickies, then all normal threads[/sql]
I don't really see anything too wrong with my current system. But feel free to analyse it or whatever if you want
Cheers,
Jack.