Suggestion to speed imrpovement

[Fast Lane!]

One of the "slowest" IPB queries is the member profile page when people view the tab to see a members recent topics/posts. The MySQL Select statement (even with indexing) takes some time on a large DB with lots of members. My suggestion to reduce this is to add a little "up front" work to reduce these query loads. Basically I am suggesting adding two columns to one of the tables (members, members_extra, etc.) that contain the last ten topics/posts the member has posted. The list could be in a single cell (comma separated list) and easily loaded with a single query. PHP can then explode the list and treat it as a FIFO and add a new post / topic ID and remove the old. Then when you need to load a persons recent topics/posts you simply load this list, explode it in PHP and then load only those topics with mysql. It is much less CPU intensive to do this and only adds minor upkeep/load on the front end. It is similar to the concept of AJAX / XML where you use some continuous and predictable loading to prevent irregular and heavy loads that you can not predict.

Thoughts? I know this would help me (5-6GB database for my largest site).

[backdream]

ALso to the post view.

14-16 querise and 0.8 second.

[Luke]

It's not that simple. You can't cache the results because of permissions. One user viewing the profile may not have access to posts in a particular form, so the results would be different from user to user.

Right now IPS is focusing on eliminating bugs that prevent intended use of the application. After that, they can spend time auditing the queries changing/adding necessary indexes to improve performance. It's really a matter of making sure that file sorting is not occurring, if possible.

One thing I do recommend them doing though is having some sort of archiving system for the posts and topics table. With large boards these tables (the posts one especially) gets really large. In effect, the indexes get really large. The purpose of these indexes is to speed up access time. When the indexes get really large, this really defeats their purpose.

A way to solve this is by moving outdated posts and topics to a "posts_archive" and "topics_archive" table, which would be almost identical to the originals. Posts a year old or more (configurable) would be moved to this archive table. The main tables would be used by the community, then when a topic falls off the face of the earth, it gets moved to this other table.

The thing that usually ends up happening is posts table get really large, and admins end up pruning them to improve access time. If there was an automated archiving system in place, old data could be kept, but in another less active table, and it could happen automatically. Admins would never have to prune again, and data is kept. Essentially archived posts would not show up in the pagination (just like you can limit the pagination now). Searching would not show these archived posts, unless a "search archive" option is enabled. Viewing posts with the standard link with cause a 301 redirect to a link with an archived marker in it. Posting a reply would move it to the active table, unless an admin has an option to not allow archived topics to be bumped. Archived topics/posts would also not show up in profiles.

That would really improve performance for large boards ;)

[bfarber]

Post archiving has been on the drawing board for a (really) long time. It's high on our priority list, but it's also very difficult and very invasive. Let's just say it's still on our to-do list for a future version.


As Luke said, however, it's difficult to cache this sort of data due to permissions.