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

[telc]

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.

# 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]

Originally Posted by telc

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]

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]

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]

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]

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]

Originally Posted by Wayne Luke

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.

Thanks again Wayne.