[eluser]boltsabre[/eluser]
As mentioned caching is a good thing, but how you'll go about getting the data to cache will depend on a few things, and it'll be up to you to do your own testing to see what is quicker.
Without knowing your complete DB forum table schemas, the amount of data stored in your tables, the number of hit's your getting a minute, the speed of your DB server, etc, we can only speculate which method will be quicker.
The two obvious solutions are:
1. As WanWiz said, create a new column in your category table called "total_topics" (or something similar) and increment it each time there is a new topic, and de-increment when a topic is deleted/archived. (But this means each and every time there is a new topic, you're also making a new 'update' query to the category table to write the increment, how much does this slow things down for your users?). Then whenever your cache updates (every 5 minutes for this?) it just needs to get the 'total_topics' from your category table.
2. Create a new column in your topics table called deleted (tiny_int, length 1, default 0, indexed), and whenever a user deletes a topic set this value to 1. Then to get your total_posts your sql would look something like
Code:
$this->db->where('cat_id', $id);
$this->db->where('deleted', 0);
$this->db->from('topics');
return $this->db->count_all_results();
This has the advantage to the user/your db that every time there is a new topic you're not updating the total column count in your category table as mentioned in the 1st suggestion above. However, it does mean that when you are getting your total count for your cache it won't be as quick as in the above scenario.
So... it's a bit of a trade of... more DB hits but a quicker count for your cache (scenario 1), or less DB hits but a slower count for your cache (scenario 2). As I said, you'll have to do your own testing to see which will work best for you and your users.
Also... lets say you have 20 different categories, and you have a page that lists all of them with their counts, one would assume that scenario 1 will reap the most benefits as you're just getting the details from 20 different table rows (as opposed to number 2 which would involve a querying your entire topics table with a COUNT, a GROUP to get the cat_ids grouped together and a WHERE clause to figure out which are deleted). But if you're not listing all 20 together (just listing one at a time on separate pages) then number 2 might be the best way to go... just depends on your requirements, you're tables, current and expected amounts of data, server speeds, etc. Some food for thought - time to get testing!!!
