Topic: 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.

Re: limiting search results (biggest punbb problem for me)

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'

3 (edited by gorsan 2009-03-24 13:06)

Re: limiting search results (biggest punbb problem for me)

thank you so much is there any solution for punbb 1.2 also ?

Re: limiting search results (biggest punbb problem for me)

Add Limit 50 to the end of the query where it retives the posts/topics.

Sorry. Unactive due to personal life.

5 (edited by gorsan 2009-03-25 03:21)

Re: limiting search results (biggest punbb problem for me)

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.

6 (edited by gorsan 2009-04-03 20:03)

Re: limiting search results (biggest punbb problem for me)

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 DESC

but 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);
            }

Re: limiting search results (biggest punbb problem for me)

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.

8

Re: limiting search results (biggest punbb problem for me)

@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.

9

Re: limiting search results (biggest punbb problem for me)

gorsan wrote:

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.