Here is my first slow query:

# Query_time: 25  Lock_time: 0  Rows_sent: 8  Rows_examined: 149647
SELECT `t`.*, COUNT(`p`.`id`) AS `postCount`, MAX(`p`.`createStamp`) AS `createStamp`
            FROM `ow_forum_topic` AS `t`
            LEFT JOIN `ow_forum_group` AS `g`
            ON (`t`.`groupId` = `g`.`id`)
            LEFT JOIN `ow_forum_section` AS `s`
            ON (`s`.`id` = `g`.`sectionId`)
            LEFT JOIN `ow_forum_post` AS `p`
            ON (`t`.`id` = `p`.`topicId`)
            WHERE `s`.`isHidden` = 0
            GROUP BY `p`.`topicId`
            ORDER BY `createStamp` DESC
            LIMIT 8;

This is triggered on my index page, where I set it to show the latest 8 topics. As you can see I have 149.647 posts on my site, and here you can see the query is extremely bad written in trying to get all the posts on my site. There should be at least a where clause by limiting the posts to some kind of date limit to bring this back to about 100 posts at most.

Here is my optimized query, where I ask for the posts from at least 30 days old:

SELECT `t` . * , COUNT( `p`.`id` ) AS `postCount` , MAX( `p`.`createStamp` ) AS `createStamp`
FROM `ow_forum_topic` AS `t`
LEFT JOIN `ow_forum_group` AS `g` ON ( `t`.`groupId` = `g`.`id` )
LEFT JOIN `ow_forum_section` AS `s` ON ( `s`.`id` = `g`.`sectionId` )
LEFT JOIN `ow_forum_post` AS `p` ON ( `t`.`id` = `p`.`topicId` )
WHERE `s`.`isHidden` =0
AND createStamp > UNIX_TIMESTAMP( ) -2592000
GROUP BY `p`.`topicId`
ORDER BY `createStamp` DESC
LIMIT 8

When running this query in mysql it now took about 1.5 seconds instead of the original 12.8 seconds! So this is already a huge win :).

I also added an index to the "createStamp" column to even improve more the performance:

ALTER TABLE `ow_forum_post` ADD INDEX `timestamp_idx` ( `createStamp` )

And WOW now the query only takes about 0.012 seconds. So this is the biggest win already.

With the big index, the old query still takes about 10 seconds, so that is not the biggest win.

So I went down digging the code to find the query.

The problematic file is here:
/var/www/html/ow_plugins/forum/bol/topic_dao.php

In this file you can see:

    public function findLastTopicList( $limit, $excludeGroupIdList = null )
    {
        $postDao = FORUM_BOL_PostDao::getInstance();
        $groupDao = FORUM_BOL_GroupDao::getInstance();
        $sectionDao = FORUM_BOL_SectionDao::getInstance();

        $excludeCond = $excludeGroupIdList ? ' AND `g`.`id` NOT IN ('.implode(',', $excludeGroupIdList).') = 1' : '';

        $query = "
            SELECT `t`.*, COUNT(`p`.`id`) AS `postCount`, MAX(`p`.`createStamp`) AS `createStamp`
            FROM `" . $this->getTableName() . "` AS `t`
            LEFT JOIN `" . $groupDao->getTableName() . "` AS `g`
            ON (`t`.`groupId` = `g`.`id`)
            LEFT JOIN `" . $sectionDao->getTableName() . "` AS `s`
            ON (`s`.`id` = `g`.`sectionId`)
            LEFT JOIN `" . $postDao->getTableName() . "` AS `p`
            ON (`t`.`id` = `p`.`topicId`)
            WHERE `s`.`isHidden` = 0 ".$excludeCond."
            GROUP BY `p`.`topicId`
            ORDER BY `createStamp` DESC
            LIMIT ?
        ";

        return $this->dbo->queryForList($query, array($limit));
    }

Now update this with:

    public function findLastTopicList( $limit, $excludeGroupIdList = null )
    {
        $postDao = FORUM_BOL_PostDao::getInstance();
        $groupDao = FORUM_BOL_GroupDao::getInstance();
        $sectionDao = FORUM_BOL_SectionDao::getInstance();

        $excludeCond = $excludeGroupIdList ? ' AND `g`.`id` NOT IN ('.implode(',', $excludeGroupIdList).') = 1' : '';

        $query = "
            SELECT `t`.*, COUNT(`p`.`id`) AS `postCount`, MAX(`p`.`createStamp`) AS `createStamp`
            FROM `" . $this->getTableName() . "` AS `t`
            LEFT JOIN `" . $groupDao->getTableName() . "` AS `g`
            ON (`t`.`groupId` = `g`.`id`)
            LEFT JOIN `" . $sectionDao->getTableName() . "` AS `s`
            ON (`s`.`id` = `g`.`sectionId`)
            LEFT JOIN `" . $postDao->getTableName() . "` AS `p`
            ON (`t`.`id` = `p`.`topicId`)
            WHERE `s`.`isHidden` = 0 ".$excludeCond."
            and createStamp > UNIX_TIMESTAMP()-2592000
            GROUP BY `p`.`topicId`
            ORDER BY `createStamp` DESC
            LIMIT ?
        ";

        return $this->dbo->queryForList($query, array($limit));
    }

Wow, wat a big boost this gives! :).

sounds good can you attach the files  id love to give it a try i have been go mad try make my site run faster

Sure, see atached. Make sure you are running the same version of the forum plugin.

I am running on:

<?xml version="1.0" encoding="utf-8"?>

<plugin>
    <name>Forum</name>
    <key>forum</key>
    <description>Simple discussion boards for users</description>
    <author>Oxwall Foundation</author>
    <authorEmail>plugins@oxwall.org</authorEmail>
    <authorUrl>http://www.oxwall.org/foundation</authorUrl>;
    <developerKey>e547ebcf734341ec11911209d93a1054</developerKey>
    <build>7000</build>
    <copyright>(C) 2009 Oxwall Foundation. All rights reserved.</copyright>
    <license>The BSD License</license>
    <licenseUrl>http://www.opensource.org/...hp</licenseUrl>;
</plugin>

oh is only for forums i that was speed up whole site lol

i dont use forums 

Well I found another problem with the forum performance when viewing the forum page. It opens up all posts, by trying to count the number of topics and posts on the forum home page:

# Time: 140602 12:30:23
# User@Host: ox[ox] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 17374
SELECT COUNT(`p`.`id`) FROM `ow_forum_topic` AS `t`
                LEFT JOIN `ow_forum_post` AS `p`
                ON ( `t`.`id` = `p`.`topicId` )
   WHERE `t`.`groupId` = '12';


The problem with this is, that it is executed for each forum, with each topic. So it takes also a lot of time...

Another very inneficient query, is this one, trying to find the latest topic:

# Time: 140602 13:05:32
# User@Host: ox[ox] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 33421
SELECT `p`.*, `t`.`title` FROM `ow_forum_post` AS `p`
            INNER JOIN `ow_forum_topic` AS `t` ON(`p`.`topicId`=`t`.`id`)
            WHERE `t`.`groupId` = '9'
            ORDER BY `p`.`createStamp` DESC LIMIT 1;


It seems to me, that all these little queries should be replaced by one single and optimized query, trying to find all the info needed in one go, instead of a lot of single calls.

Please take a loot at this topic to improve forum performance. 1.7 did not take anything of this into account!

have you found any other cool fixes for make whole site run faster

Well running it on nginx is definitely something you want!

yeah but is beyond me skills lol

Ask Julien if he would set it up for you Tammy

is to much to ask 

Julien, I have passed your solution about forums to our developers and they will check and fix this as soon as possible. We are not likely to include this into upcoming 1.7.1 build, but will do our best to address this issue in later updates.

Looking forward for further results from your tests.

thanks for this Julien

That sounds great Ross. I am curious to see what you did. Did you also optimize the forum front page? Or is there any location / site I can use to see the changes?