phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Going from 1st to last page in forum sections

Goto page 1, 2  Next
 
Search this topic... | Search phpBB2 Discussion... | Search Box
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Author Message
JLA
Board Member



Joined: 30 Apr 2009

Posts: 309
Location: U.S.A


flag
PostPosted: Wed Jun 09, 2010 7:41 am 
Post subject: Going from 1st to last page in forum sections

For those of you with larger sites - has anyone made any modifications to deal with going say from the 1st page in a forum to say the 25,000th page. The standard phpbb code is not too good on this and was curious how anyone is dealing with this?

Thanks

_________________
http://www.jlaforums.com
Back to top
dogs and things
Board Member



Joined: 18 Nov 2008

Posts: 621
Location: Spain


flag
PostPosted: Wed Jun 09, 2010 8:34 am 
Post subject: Re: Going from 1st to last page in forum sections

This MOD adds a dropdown that appears next to all pagination links throughout your board.

Simple, effective and easy to install, one edit to one file.



GoTo Specific Page MODDONE.zip
 Description:

Download
 Filename:  GoTo Specific Page MODDONE.zip
 Filesize:  3.52 KB
 Downloaded:  541 Time(s)


_________________
phpBB2 will never die, I hope!
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Wed Jun 09, 2010 1:18 pm 
Post subject: Re: Going from 1st to last page in forum sections

This is the best: Pagination Select List & Input Box
_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 309
Location: U.S.A


flag
PostPosted: Wed Jun 09, 2010 2:58 pm 
Post subject: Re: Going from 1st to last page in forum sections

Thanks all - we already use that mod. But what I am looking for is different.

We are noticing longer and longer query times on forum sections where the user will jump say from the 1st page to the 1000th or 5000th or 6000th page of a forum section. When the user goes from page to page - it is fine. But when jumping large amount of pages on a forum section that have a huge amount of them - that is where the issue is that we observe lies

Thanks

_________________
http://www.jlaforums.com
Back to top
Acaria
Board Member



Joined: 20 Feb 2009

Posts: 238



PostPosted: Thu Jun 10, 2010 6:35 am 
Post subject: Re: Going from 1st to last page in forum sections

Yeah, this has nothing to do with the actual Pagination part. Those Mods won't help.

The problem lies in the actual query. I see you show 25 threads per page on the forum. So to jump from page 1 to page 25,000, you're querying 625,000 rows before you even get to what you want.

That's what has to be fixed.
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 309
Location: U.S.A


flag
PostPosted: Thu Jun 10, 2010 2:11 pm 
Post subject: Re: Going from 1st to last page in forum sections

Acaria wrote:
Yeah, this has nothing to do with the actual Pagination part. Those Mods won't help.

The problem lies in the actual query. I see you show 25 threads per page on the forum. So to jump from page 1 to page 25,000, you're querying 625,000 rows before you even get to what you want.

That's what has to be fixed.


Exactly. So was wondering if anyone has been in a similar situation and what have then done to deal with it.

Thanks for taking a look at it!

_________________
http://www.jlaforums.com
Back to top
Salvatos
Board Member



Joined: 19 Feb 2009

Posts: 418
Location: Québec


flag
PostPosted: Thu Jun 10, 2010 7:25 pm 
Post subject: Re: Going from 1st to last page in forum sections

The only thing I can think of, and I don't think it's a very good idea when you really look into it but I'll throw it out there in case it gives you better ones, would be to avoid all these queries by mapping the forums. As far as I know, the issue is that viewforum has to build a list of all 625k topics in order every time it is loaded. Maybe you could add a column to the relevant tables to identify which page they are currently on. This would be generated and updated every time the first page is shown (which should be more than enough to keep it up to date). So when you would click on, for instance, "go to page 15,000", the query would only fetch the topics whose paging column indicates "15,000", instead of making the whole list again and picking the appropriate ones.

Again, this probably wouldn't work all that well and/or wouldn't speed things up much (resource optimization really isn't my strength), but maybe it can give you more constructive ideas ^^
Back to top
Sylver Cheetah 53
Board Member



Joined: 17 Dec 2008

Posts: 426
Location: Milky Way


flag
PostPosted: Sat Jun 12, 2010 9:46 am 
Post subject: Re: Going from 1st to last page in forum sections

Acaria wrote:
Yeah, this has nothing to do with the actual Pagination part. Those Mods won't help.

The problem lies in the actual query. I see you show 25 threads per page on the forum. So to jump from page 1 to page 25,000, you're querying 625,000 rows before you even get to what you want.

That's what has to be fixed.

25 threads per page is a very low number. I have 50 on mine, and it isn't such a big board.

_________________
Image link
My Forum || My Blog

phpBB2 forever! icon_smile.gif
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 309
Location: U.S.A


flag
PostPosted: Sat Jun 12, 2010 12:37 pm 
Post subject: Re: Going from 1st to last page in forum sections

Sylver Cheetah 53 wrote:
Acaria wrote:
Yeah, this has nothing to do with the actual Pagination part. Those Mods won't help.

The problem lies in the actual query. I see you show 25 threads per page on the forum. So to jump from page 1 to page 25,000, you're querying 625,000 rows before you even get to what you want.

That's what has to be fixed.

25 threads per page is a very low number. I have 50 on mine, and it isn't such a big board.


Due to the large amount of topics we have in most forum sections, it wouldn't make a difference if we had 25 of 50 or 100 topics to a page.

_________________
http://www.jlaforums.com
Back to top
Acaria
Board Member



Joined: 20 Feb 2009

Posts: 238



PostPosted: Sat Jun 12, 2010 4:12 pm 
Post subject: Re: Going from 1st to last page in forum sections

Sylver Cheetah 53 wrote:
25 threads per page is a very low number. I have 50 on mine, and it isn't such a big board.


Even if there were 50 or 1000 topics per page, it'd still have to query the same number of rows before reaching what you need.
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 309
Location: U.S.A


flag
PostPosted: Sat Jun 12, 2010 5:20 pm 
Post subject: Re: Going from 1st to last page in forum sections

Acaria wrote:
Sylver Cheetah 53 wrote:
25 threads per page is a very low number. I have 50 on mine, and it isn't such a big board.


Even if there were 50 or 1000 topics per page, it'd still have to query the same number of rows before reaching what you need.


Exactly!

_________________
http://www.jlaforums.com
Back to top
Acaria
Board Member



Joined: 20 Feb 2009

Posts: 238



PostPosted: Tue Jun 15, 2010 6:46 am 
Post subject: Re: Going from 1st to last page in forum sections

The best solution in this case is to do what PhpBB3 has done. If they're going to a very old page (far back), just start searching from the end.

Divide the last page number by two, then see if the page they're going to lies before or after this number. If it's before, query regularly. If it's past, just search in reverse. You should get noticeably faster results.
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 309
Location: U.S.A


flag
PostPosted: Tue Jun 15, 2010 5:25 pm 
Post subject: Re: Going from 1st to last page in forum sections

Acaria wrote:
The best solution in this case is to do what PhpBB3 has done. If they're going to a very old page (far back), just start searching from the end.

Divide the last page number by two, then see if the page they're going to lies before or after this number. If it's before, query regularly. If it's past, just search in reverse. You should get noticeably faster results.


Has anyone implemented this?

Dog Cow had this code posted on PHPBB regarding this exact thing

Code:

// Make sure $start is set to the last page if it exceeds the amount
if ($start < 0 || $start > $topics_count)
{
   $start = ($start < 0) ? 0 : floor(($topics_count - 1) / $board_config['topics_per_page']) * $board_config['topics_per_page'];
}

//
// New way of getting topics
//

// If the user is trying to reach late pages, start searching from the end
$store_reverse = false;
$sql_limit = $board_config['topics_per_page'];
if ($start > $topics_count / 2)
{
   $store_reverse = true;

   if ($start + $board_config['topics_per_page'] > $topics_count)
   {
      $sql_limit = min($board_config['topics_per_page'], max(1, $topics_count - $start));
   }
   
   // Select the sort order
   $sql_sort_order = 'ASC';
   $sql_start = max(0, $topics_count - $sql_limit - $start);

   $sql = "SELECT topic_id
   FROM " . TOPICS_TABLE . " USE INDEX (fid_type_lp)
   WHERE forum_id = $forum_id
      $limit_topics_time
   ORDER BY topic_type " . ((!$store_reverse) ? 'DESC' : 'ASC') . ", topic_last_post_id $sql_sort_order
   LIMIT $sql_start, ".$sql_limit;
}
else
{
   // Select the sort order
   $sql_sort_order = 'DESC';
   $sql_start = $start;

   $sql = "SELECT topic_id
   FROM " . TOPICS_TABLE . "
   WHERE forum_id = $forum_id
      $limit_topics_time
   ORDER BY topic_type " . ((!$store_reverse) ? 'DESC' : 'ASC') . ", topic_last_post_id $sql_sort_order
   LIMIT $sql_start, ".$sql_limit;
}



if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic ID list.', '', __LINE__, __FILE__, $sql);
}

$total_topics = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_list[] = (int) $row['topic_id'];
   $total_topics++;
}

$db->sql_freeresult($result);

if (sizeof($topic_list))
{
   if ( $store_reverse)
   {
      $sql_sort_order = 'DESC';
   }

   $sql = 'SELECT *
   FROM ' . TOPICS_TABLE . '
   WHERE ' . $db->sql_in_set('topic_id', $topic_list) . '
   ORDER BY topic_type ' . ((!$store_reverse) ? 'DESC' : 'ASC') . ', topic_last_post_id '.$sql_sort_order;

   if ( !($result = $db->sql_query($sql)) )
   {
         message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
   }

   while( $row = $db->sql_fetchrow($result) )
   {
      $topic_rowset[] = $row;

   }
}


Quote:

That goes right after the "select topic days" part and right before the "define censored word matches" bit. Also, be aware that the queries in that are from the Viewforum no query join mod.

You'll also need to copy the sql_in_set() method from phpBB 3's database class and paste it into your mysql4.php file for 2.


It doesn't seem to be a plugin piece of code.

Any ideas?

_________________
http://www.jlaforums.com
Back to top
Dog Cow
Board Member



Joined: 18 Nov 2008

Posts: 378


flag
PostPosted: Tue Jun 15, 2010 6:23 pm 
Post subject: Re: Going from 1st to last page in forum sections

Since you've got the viewforum with no table join MOD, you should also have the fid_type_lp index on the topics table.


FIND this code in viewforum.php:
Code:

//
// All announcement data, this keeps announcements
// on each viewforum page ...
//
$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.post_username
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_last_post_id
      AND p.poster_id = u2.user_id
      AND t.topic_type = " . POST_ANNOUNCE . "
   ORDER BY t.topic_last_post_id DESC ";
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}

$topic_rowset = array();
$total_announcements = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_announcements++;
}

$db->sql_freeresult($result);


//
// Grab all the basic data (all topics except announcements)
// for this forum
//
$sql = "SELECT *
   FROM " . TOPICS_TABLE . "
   WHERE forum_id = $forum_id
      AND topic_type <> " . POST_ANNOUNCE . "
      $limit_topics_time
   ORDER BY topic_type DESC, topic_last_post_id DESC
   LIMIT $start, ".$board_config['topics_per_page'];
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}

$total_topics = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_topics++;
}

$db->sql_freeresult($result);


REPLACE it with the code in your post. You might need to make sure that the $total_topics variable gets incremented in the fetchrow loop.

One side-effect is that announcements will only appear at the top of the first page, instead of at the top of all pages.

Do note, others, that these code changes assume that one has installed the above mentioned MOD.

_________________
Moof!
Lincoln's Tomb, Oak Ridge Cemetery, Springfield ILMac 512K BlogMac GUI
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 309
Location: U.S.A


flag
PostPosted: Tue Jun 15, 2010 6:52 pm 
Post subject: Re: Going from 1st to last page in forum sections

Dog Cow wrote:
Since you've got the viewforum with no table join MOD, you should also have the fid_type_lp index on the topics table.


FIND this code in viewforum.php:
Code:

//
// All announcement data, this keeps announcements
// on each viewforum page ...
//
$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.post_username
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_last_post_id
      AND p.poster_id = u2.user_id
      AND t.topic_type = " . POST_ANNOUNCE . "
   ORDER BY t.topic_last_post_id DESC ";
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}

$topic_rowset = array();
$total_announcements = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_announcements++;
}

$db->sql_freeresult($result);


//
// Grab all the basic data (all topics except announcements)
// for this forum
//
$sql = "SELECT *
   FROM " . TOPICS_TABLE . "
   WHERE forum_id = $forum_id
      AND topic_type <> " . POST_ANNOUNCE . "
      $limit_topics_time
   ORDER BY topic_type DESC, topic_last_post_id DESC
   LIMIT $start, ".$board_config['topics_per_page'];
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}

$total_topics = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_topics++;
}

$db->sql_freeresult($result);


REPLACE it with the code in your post. You might need to make sure that the $total_topics variable gets incremented in the fetchrow loop.

One side-effect is that announcements will only appear at the top of the first page, instead of at the top of all pages.

Do note, others, that these code changes assume that one has installed the above mentioned MOD.


Is the code I posted from PHPBB in my last post current. I tried replacing the section with that and view forum had a blank page.

Thanks

_________________
http://www.jlaforums.com
Back to top
Display posts from previous:   
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT
Goto page 1, 2  Next
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0342 seconds using 17 queries. (SQL 0.0027 Parse 0.0026 Other 0.0289)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo