Optimizing Phpfox - Tip #2

The feed seems to be the most resource hungry feature so far. If Timeline is enabled , when going to a profile the script needs to find which years have content so it can display the Timeline years block; this can span up to 20 queries to the database in some cases and while it makes proper use of indexes and conditions this is a load we can save in 2 ways:

1) Disable Timeline
2) Disable the Time block, this is perhaps the most feasible option, if you have timeline enabled it must be for a reason. To disable this specific block go to AdminCP -> CMS -> Block Manager, then click on profile.index and disable "Feed Timeline":

 you will keep the timeline look but the year selector wont be there

With this small change your site will be using a lot less resources and it will contribute to keeping it more stable and efficient.

Note: In case you are wondering, the queries that come from this block do in fact get cached, one cache file per user, but the cache file (specific to a user) is deleted after that user posts something that creates a feed.

Optimizing Phpfox - Tip #1

Lately I've been running into little "tricks" that can help a phpfox site perform better, I will try to add them here as I find them.
Today I have perhaps the most important one that I have seen so far, it came after debugging this database query:

explain extended SELECT feed.*, f.friend_id AS is_friend, 
apps.app_title,  u.user_id, u.profile_page_id, 
u.server_id AS user_server_id, u.user_name, u.full_name, 
u.gender, u.user_image, u.is_invisible, u.user_group_id, 
u.language_id

FROM phpfox_feed AS feed
JOIN phpfox_user AS u
        ON(u.user_id = feed.user_id)
LEFT JOIN phpfox_friend AS f
        ON(f.user_id = feed.user_id 
AND f.friend_user_id = 1)
LEFT JOIN phpfox_app AS apps

        ON(apps.app_id = feed.app_id)


WHERE feed.time_stamp > '0' AND feed.feed_reference = 0
GROUP BY feed.feed_id
ORDER BY feed.time_update DESC
LIMIT 10

Why is it such a naughty query? well the feed table is the main table here, but the only two conditions to filter it are the time_stamp and the feed_reference. Most of the time feed_reference will be 0 so this isn't a great filter, and time_stamp > 0 is trivial, in fact ignored by MySQL. So, many times this will run through the entire feed table, which is very likely to host hundreds of thousands of records.

Luckily we have a setting in the AdminCP that can help with this, "Feed Limit (Days)", this tiny little setting will help you greatly to improve performance. What it does is to limit the feeds to a number of days in the past. One way to find a good value for this is to do this check, add a photo to the feed or something that you can easily identify, come tomorrow and look for that feed, if you cannot find it in the first page (before the ajax load ) then you think that 1 day is enough, but for safety check the next page of feeds, if you cannot find it in the 3d page then my advise is to set this to 3 so it looks a maximum of 3 days in the past.

Hope it helps