I think this could really help us bigboards if VB or a VB.org implemented this

[telc]

http://www.vbulletin.com/forum/showthread.php?t=242202

My forum is very large, and when I enabled the slow_query log in mysql. I noticed that searches that match a large # of posts could take a long time to execute. (this is extremly CPU intensive and causes a downward spiral at times)

This is the query that gives me the most trouble, it is in search.php

I had to cut out the postids because the query was over 350,000 characters and too large to post here.

There was over 40,000 matching postids being passed into that "IN" clause.

I do have the search results limit set to 500 in admincp but that does not have any affect on this query.


# Query_time: 16 Lock_time: 0 Rows_sent: 43736 Rows_examined: 87970

SELECT thread.threadid, thread.forumid, post.userid
FROM thread AS thread INNER JOIN post AS post ON(thread.threadid = post.threadid ) WHERE post.postid IN (MANY POST IDS) AND post.visible = 1;


Propasal:

If a users search matches over X number of posts, the user could be prompted to refine the query.

It would be nice if X this could be an admincp setting, it would stop large #'s of postid's from being passed into that "IN" clause.

[Eikinskjaldi]

I think I'm looking at the right section in search.php, but might you be able to get a line number for the query that's causing the problem?

[telc]

Wayne said this is the query: https://vborg.vbsupport.ru/showpost.php?p=1332575&postcount=4

I think it would be an easy Add-on:

Search.php: Line1296


$post_query_logic[] = "post.postid IN(" . implode(',', $requiredposts) . ")";



If count() of $requiredposts > X prompt user to refine query.

This would stop thousands of postid's from being passed to that "IN" clause.

[Eikinskjaldi]

I'd already played with that, throwing
if(count($requiredposts > 0)) {
eval(standard_error(fetch_error('please_refine_sea rch_query')));
}
just above that line. Unfortunately it doesn't seem to do much of anything. Let me know if it works for you.

[Paul M]

Line 1296 is not the only place that postids is built into the query logic, see also lines 1050 & 1123.

[telc]

I'd already played with that, throwing
if(count($requiredposts > 0)) {
eval(standard_error(fetch_error('please_refine_sea rch_query')));
}
just above that line. Unfortunately it doesn't seem to do much of anything. Let me know if it works for you.

I will try when I get home, but it should be:

if(count($requiredposts) > 0) {

not

if(count($requiredposts > 0)) {

[Analogpoint]

Without looking at it at depth, I think the following would work. Edit your search.php file with the following changes. Be sure to back up your search.php file before doing this, so if it doesn't work right, you can revert to the regular version. Warning: this code is untested.

First define a phrase called searchrefinequery with your error message, maybe: "Your search returned gazillions of posts, try being more specific, thanks".

Also, change the number 1000 to whatever suits your fancy.

(1)

Add this:
if (count ($ANDs) > 1000)
{
eval(standard_error(fetch_error('searchrefinequery ')));
}

Right before this on line 1050:
$post_query_logic[100] = 'post.postid IN(' . implode(',', $ANDs) . ')';

(2)

Add this:
if (count ($postids) > 1000)
{
eval(standard_error(fetch_error('searchrefinequery ')));
}

Right before this on line 1123:
$post_query_logic[] = 'post.postid IN(' . implode(',', $postids) . ')';


(3)

Add this:
if (count ($requiredposts) > 1000)
{
eval(standard_error(fetch_error('searchrefinequery ')));
}

Right before this on line 1296:
$post_query_logic[] = "post.postid IN(" . implode(',', $requiredposts) . ")";