vb.org Archive - Anyone know where they query is called from in vb3.6.x

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - Anyone know where they query is called from in vb3.6.x (https://vborg.vbsupport.ru/showthread.php?t=157048)

telc	09-04-2007 11:11 PM

Anyone know where they query is called from in vb3.6.x

I was looking at my slow query log and found several queries similiar to this one:

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

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;

Eikinskjaldi

09-04-2007 11:37 PM

Quote:

Originally Posted by telc (Post 1332540)

I was looking at my slow query log and found several queries similiar to this one:

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

Put your code in code tags so it can be quoted back at you.

The query in question lives in a plugin, not any of the vb code (I did a search on the vb code base, which is to say I might be wrong).

The query is slow because it contains over 350 000 characters. The issue is why so many postids, not is there a problem with the query.

telc	09-05-2007 12:02 AM

I don't have any plug-ins, I am just trying to locate where in the VB code this query is. It is a very large forum, any only occasionally this query is slow.

They query may be formatted different in the php file so I can't search for the entire thing such as "SELECT thread.threadid, thread.forumid, post.userid"

Wayne Luke

09-05-2007 12:31 AM

Looks like one of the search queries.

Actual query is as line 1503 in search.php within vBulletin 3.6.8, the building of the query is before that.

PHP Code:


		
			
     $threads = $db->query_read_slave("
      SELECT
      " . implode(', ', $thread_select_logic) . "
      FROM " . TABLE_PREFIX . "thread AS thread $userid_index
      " . ((!empty($post_query_logic) OR !empty($post_join_query_logic)) ? "INNER JOIN " . TABLE_PREFIX . "post AS post ON(thread.threadid = post.threadid $post_join_query_logic)" : "") . "
      " . (!empty($querylogic) ? "WHERE " . implode(" AND ", $querylogic) : "") . "
      $nl_query_limit
     ");

Searching is fairly intensive. Might want to reduce the number of results returned if you don't already.

telc	09-05-2007 12:37 AM

Thanks Wayne,

I have the limit # of results set to 500 in the Admincp.

I just counted the # of postid's in that IN clause and there was 44,486 postids.

I am not sure dropping that 500 down would have much affect.

Wayne Luke

09-05-2007 02:04 AM

Track your slow query log and look at your recent searches that happen about the same time. Check the words that are being searched for and if they are overly common, remove them from the search index. For instance on a vBulletin related website, you shouldn't allow searches for vBulletin as it will occur in too many places to be useful. If your site is about flowers than flower can be eliminated. It may mean that your users have to get more creative but that shouldn't cause too much harm. They should get more relevant results.

You can see what people searched for in the query column of your search table. This is cleared every hour so you might want to adjust the query for this in includes/cron/cleanup.php. It is this code:

PHP Code:


		
			
//searches expire after one hour
$vbulletin->db->query_write("
 DELETE FROM " . TABLE_PREFIX . "search
 WHERE dateline < " . (TIMENOW - 3600) . "
 ### Remove stale searches ###
");

Having these records stored for a longer period of time during your investigation shouldn't cause too many problems and would allow you to track things down easier.

telc	09-05-2007 02:07 AM

Quote:

Originally Posted by Wayne Luke (Post 1332602)

PHP Code:


		
			
//searches expire after one hour

$vbulletin->db->query_write("

 DELETE FROM " . TABLE_PREFIX . "search

 WHERE dateline < " . (TIMENOW - 3600) . "

 ### Remove stale searches ###

");

Having these records stored for a longer period of time during your investigation shouldn't cause too many problems and would allow you to track things down easier.

Thanks again Wayne.

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

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01136 seconds Memory Usage 1,747KB 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 (1)bbcode_code_printable (3)bbcode_php_printable (2)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
Messages: