Topic: MySQL Query Caches?
idea: Why don't you use the MySQL query cache to speed up queries? Especially static queries like the query that fetches the category names, would speed up.
I know, this is MySQL-specific, but I'm sure the other database systems have something comparable.
To optimize PunBB for both, query_cache_type = ON(caching all queries) and query_cache_type = DEMAND(only caching special marked queries), you could give each query flag:
* SQL_CACHE for queries that speed up much, when cached, e.g. the following query is executed 10x faster on my system after being cached
SELECT c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster FROM categories AS c INNER JOIN forums AS f ON c.id=f.cat_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=1) WHERE fp.read_forum IS NULL OR fp.read_forum=1 ORDER BY c.disp_position, c.id, f.disp_position
* SQL_NO_CACHE for queries that don't speed up or even slow down, e.g. queries containing the current UNIX-timestamp
SELECT o.* FROM online AS o WHERE o.logged<1205706380
In this case, the system would NOT slow down under any circumstances, because "dangerous" queries are marked with NO_CACHE. The system can only speed up, because queries are being cached when the MySQL is ON or in DEMAND mode. If the query cache is disabled, MySQL simply ignores the cache-flag.
second idea: To optimize time-based queries like the one above, which fetches users, that were active during the last 5 minutes (time()-300), we have to make the condition change as less as possible. The trick is the following:
No condition like:
a < time()
the idea: create 5-minute-steps
a < time() - (date('i')%5)*60 - date('s')
this piece of code subtracts the time passed since the last 5min-mark from the current timestamp - result:
we get 16:45, on 16:45, 46, 47, 48 and 49. It requires only one cache update every 5min.
The bad side of it: on 16:46, it checks activity during the last minute.. By subtracting another 5min of it, we get the online activity for the last 5 - 9min. I think that timerange is acceptable.
$last_step = $time - (date('i')%5)*60 - 300 - date('s');