You are not logged in. Please login or register.
PunBB Forums → PunBB 1.2 troubleshooting → limiting search results (biggest punbb problem for me)
is it possible to limit search results
go in this url and see http://mypunbb.com/search.php?action=show_unanswered
it opens in 30 whole seconds
well in my own forum it is much worse
how can we limit search results for the first 50 topics for example
P.S. this is same in punbb 1.3 i should have posted this at bugs.
Anyone please help
thank you.
Go to <FORUM_ROOT>/inlcude/search_functions.php go to line 643 and replace this code
'ORDER BY' => 't.last_post DESC'with this
'ORDER BY' => 't.last_post DESC',
'LIMIT' => '50'thank you so much is there any solution for punbb 1.2 also ?
Add Limit 50 to the end of the query where it retives the posts/topics.
if ($show_as == 'posts')
{
$substr_sql = ($db_type != 'sqlite') ? 'SUBSTRING' : 'SUBSTR';
$sql = 'SELECT p.id AS pid, p.poster AS pposter, p.posted AS pposted, p.poster_id, '.$substr_sql.'(p.message, 1, 1000) AS message, t.id AS tid, t.poster, t.subject, t.question, t.last_post, t.last_post_id, t.last_poster, t.num_replies, t.forum_id, t.labels FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'topics AS t ON t.id=p.topic_id WHERE p.id IN('.$search_results.') ORDER BY '.$sort_by_sql.'DESC LIMIT 50';
}
else
//$sql = 'SELECT t.id AS tid, t.poster, t.subject, t.question, t.last_post, t.last_post_id, t.last_poster, t.num_replies, t.closed, t.forum_id FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'topics AS t ON t.id=p.topic_id WHERE t.id IN('.$search_results.') GROUP BY t.id, t.poster, t.subject, t.question, t.last_post, t.last_post_id, t.last_poster, t.num_replies, t.closed, t.forum_id'.$group_by_sql.' ORDER BY '.$sort_by_sql;
$sql = 'SELECT t.id AS tid, t.poster, t.subject, t.question, t.sticky, t.last_post, t.last_post_id, t.last_poster, t.num_replies, t.closed, t.forum_id, t.labels FROM '.$db->prefix.'topics AS t WHERE t.id IN('.$search_results.') ORDER BY '.$sort_by_sql.'DESC LIMIT 50';and then i had to delete this line
$sql .= ' '.$sort_dir.' LIMIT '.$start_from.', '.$per_page;
well it did not optimize anything or searches i may be doing something wrong
thanks anyway.
could you please help me
my users are forcing me to open search.php again
it is impossible without limiting 2 million posts
i found i have to limit these querries and i found i have to do something like this link
http://lists.mysql.com/mysql/209407
example:
>> SELECT points
>> FROM results
>> WHERE
>> (
>> SELECT count(*)
>> FROM results as R
>> WHERE R.person_id = results.person_id AND R.points > results.points
>> ) <3
>> ORDER BY person_id, points DESCbut cant do it, the lines i have to change is below
if ($show_as == 'topics')
{
$result = $db->query('SELECT t.id FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'topics AS t ON t.id=p.topic_id INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND p.id IN('.implode(',', $search_ids).')'.$forum_sql.' GROUP BY t.id', true) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());
$search_ids = array();
while ($row = $db->fetch_row($result))
$search_ids[] = $row[0];
$db->free_result($result);
$num_hits = count($search_ids);
}
else
{
$result = $db->query('SELECT p.id FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'topics AS t ON t.id=p.topic_id INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND p.id IN('.implode(',', $search_ids).')'.$forum_sql, true) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());
$search_ids = array();
while ($row = $db->fetch_row($result))
$search_ids[] = $row[0];
$db->free_result($result);
$num_hits = count($search_ids);
}i am being forced to convert smf now, which seems the correct way to go since i could not upgrade to punbb 1.3 because of utf8 problems which i cant really solve.
stats.punres will have a new number 1 forum soon.
@gorsan: Do you get an error when trying to upgrade to 1.3?
If so, Let me know what the error is and I will try and help you.
i am being forced to convert smf now, which seems the correct way to go since i could not upgrade to punbb 1.3 because of utf8 problems which i cant really solve.
Why? You haven't even added a limit to those two queries you posted above, so obviously they will return all matches.
That first query:
$result = $db->query('SELECT t.id FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'topics AS t ON t.id=p.topic_id INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND p.id IN('.implode(',', $search_ids).')'.$forum_sql.' GROUP BY t.id', true) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());should be:
$result = $db->query('SELECT t.id FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'topics AS t ON t.id=p.topic_id INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND p.id IN('.implode(',', $search_ids).')'.$forum_sql.' GROUP BY t.id LIMIT 50', true) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());Do the same for the second query.
stats.punres will have a new number 1 forum soon.
The point? This board isn't commercial, hence any responses are from people volunteering their time to responding. That approach is hardly liable to make people jump at your behest.
PunBB Forums → PunBB 1.2 troubleshooting → limiting search results (biggest punbb problem for me)
Powered by PunBB, supported by Informer Technologies, Inc.