Hacking up Search by Username

I have replaced vBulleting's internal fulltext search with one powered by the Sphinx engine. It works fantastic.

It does not work for search for all threads started by user and search for posts by user.

We have a post table of over 8 million posts, and the table locking has caused significant delays for the website. We've had to disable search.

I'm ready for drastic measures. Anyone have advice?

[fastforward]

Originally Posted by Rayn21

I have replaced vBulleting's internal fulltext search with one powered by the Sphinx engine. It works fantastic.

It does not work for search for all threads started by user and search for posts by user.

We have a post table of over 8 million posts, and the table locking has caused significant delays for the website. We've had to disable search.

I'm ready for drastic measures. Anyone have advice?

This post references that issue.
https://vborg.vbsupport.ru/showthrea...er#post1150437

Use the latest code provided by orban in that thread. The search by user works fine for me.

[Rayn21]

If you are using orban's code, it uses the regular vb searching for keywordless username searches and sphinx for everything else. If you have a smaller post table, you may not notice problems with it. Unfortunately, I had to remove anything that didn't use sphinx because my post table is a monster.

Regarding adding that keyword, that would require modification of orban's code as well as search.php; I don't have enough grasp on the code to make those modifications.

[telescopi]

I've spent most of today doing it - going off the basic idea in orbans thread. I think I've listed every change I made.

The first bit in sphinx.conf is replace pagetext in the two post queries with this:

concat(pagetext,' username_', post.username,' startername_',thread.postusername)

You basically get then instead of just "message", "message username_myname startername_hisname" in your sphinx index.

Then you edit your search.php, find this block of code:

if ($vbulletin->GPC['userid'] AND $userinfo = fetch_userinfo($vbulletin->GPC['userid']))
	{
		$vbulletin->GPC_exists['searchuser'] = true;
		$vbulletin->GPC['searchuser'] = unhtmlspecialchars($userinfo['username']);
	}

and add this after it:

// If username does not exist, skip sphinx and let the normal search routine spit it out
      if ($testuser = $db->query_first_slave("SELECT userid, username, posts FROM " . TABLE_PREFIX . "user WHERE username = '" . $vbulletin->GPC['searchuser'] . "'")){

        if ($vbulletin->GPC['searchuser'] != '') {
             if ($vbulletin->GPC['starteronly']) {
                $vbulletin->GPC['query'] = $vbulletin->GPC['query'] . ' startername_' . $vbulletin->GPC['searchuser'];
             }
             else {
                $vbulletin->GPC['query'] = $vbulletin->GPC['query'] . ' username_' . $vbulletin->GPC['searchuser'];
             }
             $vbulletin->GPC['searchuser'] = '';
        }
      }

That will basically do it - your faking a keyword search from your user search. If the search is not exact, it goes on to the standard vbulletin search, if the username does not exist it goes on to the standard vbulletin search.

To tidy it up I added after

$displayWords = '<b><u>' . implode('</u></b>, <b><u>', $display['words']) . '</u></b>';

$displayWords = str_replace('username_','', $displayWords);
        $displayWords = str_replace('startername_','', $displayWords);

To hide what it really just searched on.

I chose to not allow partial name matches (unticking exact match) as it's not that useful and can tip you over the edge if the server is heavily loaded already. To stop members using it but still allow moderators and so on I added this modification to the search template:

<if condition="is_member_of($bbuserinfo, 5, 6, 7)">
	<label for="cb_exactname"><input type="checkbox" name="exactname" value="1" id="cb_exactname" $exactnamechecked[1] />$vbphrase[exact_name]</label>
<else />
	<input type="hidden" name="exactname" value="1">
</if>

So if you aren't an admin or moderator the exact name is always on.

I also changed this in the postbit template:

<if condition="$show['search']">
		<tr><td class="vbmenu_option"><a href="search.php?$session[sessionurl]do=finduser&amp;u=$post[userid]" rel="nofollow"><phrase 1="$post[username]">$vbphrase[find_more_posts_by_x]</phrase></a></td></tr>
	</if>

to

<if condition="$show['search']">
		<tr><td class="vbmenu_option"><a href="search.php?$session[sessionurl]do=process&amp;searchuser=$post[username]&amp;u=$post[userid]" rel="nofollow"><phrase 1="$post[username]">$vbphrase[find_more_posts_by_x]</phrase></a></td></tr>
	</if>

basically changing the do= to process and adding searchuser=username. This forces the search.php into the same sphinx search when somebody clicks 'find more posts by xxxx'.

[Jah-Hools]

Can I ask why you wanted to replace fulltext?

Thanks,

[telescopi]

As the OP says - locking.

Sphinx search avoids all locking issues because it is not a part of mysql, it also helps that it is lightning fast - sub 1 second results whewre fulltext would have taken 20 seconds or more.

You also reduce the size of your database substantially as you can drop the fulltext index on the post table, this means mysql can devote it's memory to more useful indexes.

[jawatkin]

Originally Posted by telescopi

I've spent most of today doing it - going off the basic idea in orbans thread. I think I've listed every change I made.

Thank you very much for this. This not being able to search by name via Sphinx has been the only thing holding me back from putting the time into implementing it. Rapidly coming up on 2M posts and the searches are locking up mySQL during peak times.

EDIT: This works REALLY great. Will avoid the locking that I've been experiencing and the users will be happier with faster searches!

[jawatkin]

Originally Posted by telescopi

I also changed this in the postbit template:

<if condition="$show['search']">
		<tr><td class="vbmenu_option"><a href="search.php?$session[sessionurl]do=finduser&amp;u=$post[userid]" rel="nofollow"><phrase 1="$post[username]">$vbphrase[find_more_posts_by_x]</phrase></a></td></tr>
	</if>

to

<if condition="$show['search']">
		<tr><td class="vbmenu_option"><a href="search.php?$session[sessionurl]do=process&amp;searchuser=$post[username]&amp;u=$post[userid]" rel="nofollow"><phrase 1="$post[username]">$vbphrase[find_more_posts_by_x]</phrase></a></td></tr>
	</if>

basically changing the do= to process and adding searchuser=username. This forces the search.php into the same sphinx search when somebody clicks 'find more posts by xxxx'.

This didn't quite give me the desired effect (at least not the default of find more posts for user). What it gave was a partial name search and returned it as threads. I changed it to the following and it gives the desired effect:

<if condition="$show['search']">
		<tr><td class="vbmenu_option"><a href="search.php?$session[sessionurl]do=process&amp;showposts=1&amp;exactname=1&amp;u=$post[userid]&amp;searchuser=$post[username]" rel="nofollow"><phrase 1="$post[username]">$vbphrase[find_more_posts_by_x]</phrase></a></td></tr>
	</if>

[mute]

Hmm. This looks promising. My search.php is so old (we're still running 3.6.2+patches), that I don't have that bit of code in mine. I'll have to see if I can get 3.6.8's running on our install.

--------------- Added [DATE]1193940945[/DATE] at [TIME]1193940945[/TIME] ---------------

I've found a minor bug.

I've gotten this up and running, but searches on my site for say.. "Kyle" also pull up posts for the user "Kyle=agglicious".

I'm also seeing really weird results when searching for all threads by users, but I have yet to track the source of the problem down.

[mute]

Ok, so I've done a bit more investigation.

When I have the search.php blurb in that concats the startername_Username or username_Username bits to the search query, the results returned are really wonky.

We limit search results to 500 results. If I have this code running, and search for threads created by myself, I only get 27 results back ranging back about 2 months, as opposed to the full range.

If i search via the command line client, I get many more results:

[root@db2 ~]# search -c sphinx.conf startername_Kyle|grep index
index 'vbpost': query 'startername_Kyle ': returned 1000 matches of 2854 total in 0.005 sec
index 'vbpostarchived': query 'startername_Kyle ': returned 1000 matches of 4882 total in 0.012 sec
index 'vbpostdelta': query 'startername_Kyle ': returned 1 matches of 1 total in 0.000 sec
index 'vbthread': query 'startername_Kyle ': returned 0 matches of 0 total in 0.000 sec
index 'vbthreaddelta': query 'startername_Kyle ': returned 0 matches of 0 total in 0.000 sec

If I comment out the code and let the normal search run, it works as it should. Furthermore, I'm not entirely sure why you are running an additional query rather than just using fetch_userinfo() in the if statement that wraps this code.

Has anyone else run into these issues? I'm going to troubleshoot some more, but so far I'm not able to get this code working as the OP said it should.