Profilling my application help

[eluser]Bainzy[/eluser]
Hey everyone,

Right I have run into a little issue while I am trying to profile Dove Forums.

Basically the front page is currently made up of 18 queries, all less than 1 millisecond, however 10 of these queries are to retrieve the amount of reply's to a topic.

What I have done is created a helper for general forum functions and counting the amout of reply's is on of my functions, so here is the function in the helper file :

Code:

function countComments($TopicID)
{
    $CI=& get_instance();
    return $CI->MTopics->countComments($TopicID);
}

and here is the code in my model file :

Code:

function countComments($TopicID)
    {
        $options = array('TopicID'=>$TopicID, 'Active'=>'1');
        $query = $this->db->get_where('comments', $options);
        return $query->num_rows();
    }

What i need to know is there any other way of counting the topics rather then running 10 queries, as my concern is say there is 10,000 topics in the database, the above code is going to be executed 10,000 times just to get a number back ?

Any help on this guys ?

[eluser]smilie[/eluser]
I am not sure it could be done otherwise. However, your front page should not be presenting _all_ topics unless I am wrong. It will probably show forum categories maybe and some info about categories. Basically, there will be no need to count _everything_ you have in forum?

Other things you may consider is database optimization (good and enough indexes and primary keys, good queries (avoid select * from ...) and such). You could also consider query cashing and then run those 10 queries once per hour and cache those results. It will then not be 100% up to date - but should improve performance significantly once DB grows.

Regards,
Smilie

[eluser]kaejiavo[/eluser]
Hi,
of course it can be done another way, like

Code:

SELECT *, (SELECT COUNT(*) FROM `comments` WHERE ...) as commentCount FROM posts ...

this depends on your database structure, just for you to get the idea.

Marco