Detailed Latest Discussions in What's Going On

Below is my first attempt at creating a hack. It works perfectly on my board but as no others have tested it, I am putting it here for now. I know there are other latest x posts type hacks around, however, in my day long search of this forum, I have yet to find one that suited my needs. Therefore, I wrote my own. I offer it here. It only uses one query to return information on the last x posts including the fields (forum id, forum title, thread id, thread title, initial poster id, initial poster name, last poster id, last poster name, reply count and views). It takes into account permissions using the CANVIEW and CANVIEWOTHERS constants and properly flags the show variable if no rows are deemed showable. Have fun and please do not hesitate to comment on this. This code is entirely of my own writing although it was visually inspired by the hack in place on http://cosforums.com. Thanks.

[Erwin]

The code still does a full thread table scan - so this will cause server load problems on a large site. Just letting people know. Not a problem with small sites.

[Albus]

For those who did not understand that, let me elaborate. In a simple one table query you could select x last rows, which would not require the database server to actually 'look' at each row in the table to see if it met your criteria. That's a partial scan. In this hacks case, several tables are linked through join clauses andsome conditionals. This requires the database to scan the full table looking for sets of records that meet the search criteria, and finally get the last x rows. Now, ifyou are with us (Erwin and I) so far, here's why the script even bothers with a limit. With or without the limit, the database will initially do almost as much work, the difference is during transmission time, when the database sends the data back to the calling app (php page in this case). All that data is transmitted via the network. If we limit the data before it's sent, it means less network traffic, sure, but the relative hit on server-side performance is about the same. What Erwin says is true however. Unfortunately, in developing a general use hack, there are some sacrifices we must make in order to make it useful to the most people and trust that those with larger boards have also been around long enough to tailor a hack if required.



That was more than I intended on posting...oh well.

[Boofo]

Can it be fixed to satisfy both large and small sites?

[Erwin]

Not really. At least, not without adding new columns and queries, and making it work like lastpost - basically caching the latest post info.

[Albus]

It could be re-written to do most of the work in the php script. Write an sql statment that returns the last x (50 or so only) threads period. Then, in the php code, make the loop test the thread for perms first to avoid any extra processing time, then simply write a few small queries that get the extra info, like forum info, post info, and deletionlog info. The key here being simple queries with no joins. This would mean more, but less intensive db hits. If you have your display limit set to a small amount, like 8 or 10, then it shouldn't be too bad as your only getting the data you need when you need it. If you interested in this, I'll write an alternate version and you can test the efficiency of it.

[Albus]

On second look Erwins solution is better. The first real check in this hack is for permissions which uses the forum id. That being the case, displayed or not, that is at least two more queries per row returned by my example above to get the forum id. I'll look into this further.

[Erwin]

Originally Posted by Serenarules

On second look Erwins solution is better. The first real check in this hack is for permissions which uses the forum id. That being the case, displayed or not, that is at least two more queries per row returned by my example above to get the forum id. I'll look into this further.

I just had a look at your site - your style is awesome. Well done!

[Albus]

Many thanks!

[Erwin]

I've figured out a way to pull the latest post from each forum and place it anywhere on the forumhome - without adding any new queries at all - but it does not work like a normal latest thread box as multiple threads in the same forum will not show up - only the latest one would. This is because only the lastpost is cached for each forum. I'll play around with it...

[Erwin]

Originally Posted by Serenarules

On second look Erwins solution is better. The first real check in this hack is for permissions which uses the forum id. That being the case, displayed or not, that is at least two more queries per row returned by my example above to get the forum id. I'll look into this further.

My version of this hack hardcoded the permissions. Which saves on database queries. I just excluded certain forums in the actual query itself. Not as elegant, but works.