vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   Big Board Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=172)
-   -   I think this could really help us bigboards if VB or a VB.org implemented this (https://vborg.vbsupport.ru/showthread.php?t=157190)

telc 09-06-2007 07:05 PM
I think this could really help us bigboards if VB or a VB.org implemented this
 
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.

Code:
# 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 09-06-2007 07:18 PM
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 09-06-2007 07:36 PM
Wayne said this is the query: https://vborg.vbsupport.ru/showpost....75&postcount=4

I think it would be an easy Add-on:

Search.php: Line1296

PHP Code:
$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 09-06-2007 08:00 PM
I'd already played with that, throwing
Code:
if(count($requiredposts > 0)) {
eval(standard_error(fetch_error('please_refine_search_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 09-06-2007 08:42 PM
Line 1296 is not the only place that postids is built into the query logic, see also lines 1050 & 1123.

telc 09-06-2007 08:42 PM
Quote:
Originally Posted by Eikinskjaldi (Post 1333825)
I'd already played with that, throwing
Code:
if(count($requiredposts > 0)) {
eval(standard_error(fetch_error('please_refine_search_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:

PHP Code:
if(count($requiredposts) > 0) { 
not

PHP Code:
if(count($requiredposts > 0)) { 

Analogpoint 09-07-2007 08:51 PM
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:
PHP Code:
if (count ($ANDs) > 1000)
{
    eval(standard_error(fetch_error('searchrefinequery')));
} 
Right before this on line 1050:
PHP Code:
$post_query_logic[100] = 'post.postid IN(' . implode(',', $ANDs) . ')'; 
(2)

Add this:
PHP Code:
if (count ($postids) > 1000)
{
    eval(standard_error(fetch_error('searchrefinequery')));
} 
Right before this on line 1123:
PHP Code:
$post_query_logic[] = 'post.postid IN(' . implode(',', $postids) . ')'; 
(3)

Add this:
PHP Code:
if (count ($requiredposts) > 1000)
{
    eval(standard_error(fetch_error('searchrefinequery')));
} 
Right before this on line 1296:
PHP Code:
$post_query_logic[] = "post.postid IN(" . implode(',', $requiredposts) . ")"; 


All times are GMT. The time now is 11:32 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01222 seconds
  • Memory Usage 1,744KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (3)bbcode_code_printable
  • (9)bbcode_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (7)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete