PunBB Forums - limiting search results (biggest punbb problem for me)

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

2009-04-10T12:53:23Z

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.

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

2009-04-09T20:22:18Z

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

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

2009-04-09T11:18:48Z

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.

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

2009-04-03T19:47:14Z

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)

2009-03-25T01:52:36Z

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.

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

2009-03-24T19:05:39Z

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

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

2009-03-24T12:56:53Z

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

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

2009-03-24T09:29:33Z

Go to /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'

limiting search results (biggest punbb problem for me)

2009-03-23T23:20:44Z

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.