Sphinx Search

[weeno]

find the time to look into 3.7 it Jason?

arn

[Xorlev]

From what I've been reading, the Sphinx addon doesn't have the ability to search for posts by user, right? And the only way is to make a new field for a fulltext index?

Well, I'm not sure about Sphinx 0.9.7, but on my own (not vBulletin) site running Sphinx 0.9.8 I was able to replicate every feature of the vBulletin search and then some using only the API calls in the Sphinx PHP API. If someone could summarize what's missing in the vB Sphinx implementation I could possibly help write in the features missing. It really takes some experience to be able to work with Sphinx, the documentation is rather lacking.

[mute]

Originally Posted by Xorlev

From what I've been reading, the Sphinx addon doesn't have the ability to search for posts by user, right? And the only way is to make a new field for a fulltext index?

Well, I'm not sure about Sphinx 0.9.7, but on my own (not vBulletin) site running Sphinx 0.9.8 I was able to replicate every feature of the vBulletin search and then some using only the API calls in the Sphinx PHP API. If someone could summarize what's missing in the vB Sphinx implementation I could possibly help write in the features missing. It really takes some experience to be able to work with Sphinx, the documentation is rather lacking.

From what I gather, there is a way to "find all posts by user" and "find all threads by user" using sphinx and appending a hidden prefix to each thread. I'm not running the latest vB so I couldnt get it working properly, but I did notice when I rebuilt my indexes with the additional information it bloated the index size up quite a bit.

I think I posted a page or so back about it.

[amcd]

It is not needed as those searches do not use fulltext index. They use the index on the userid field which exists for both post and thread tables.

[mute]

Originally Posted by amcd

It is not needed as those searches do not use fulltext index. They use the index on the userid field which exists for both post and thread tables.

It most certainly IS needed. Those searches hurt on big sites, even with the indexes.

[Xorlev]

One can easily setup the index to grab userids as a filterable attribute in sphinx.conf. I suppose what I'm trying to understand is why one would use a new generated fulltext index on a column with "userid_xxxx" when they could just add it to their integer indexes. I would think the performance on the non-fulltext index would be greater, but I could be wrong.

[PSS]

Originally Posted by eoc_Jason

You have to remember though that vBulletin has to maintain backwards compability as most people are not running bleeding edge versions of PHP or MySQL. While search performance is a serious issue that the vB team has yet to address, I hope that they do offer some advanced choices for people that do know what they are doing. In short, it never hurts to hope, but don't hold you breath.

Probably next week since things will be slow and I'll be bored I'm going to download the 3.7 beta and take a look at the search code. But I'm not going to post anything based on 3.7 until it is actually released. I do plan on upgrading my forum to it as soon as it is stable / release, so you know I will be getting sphinx to work on it asap.

Thanks it would be great if you'd share the 3.7 + Sphinx info! I'm not going to use Vb inbuilt search ever again.

[Xorlev]

I thought you all that use (and program with) Sphinx might be interested to know that Shodan, the programmer of Sphinx, put up a wiki for the Sphinx community after we cajoled a bit. To show the fruits of that effort, I've written the documentation for the PHP API of Sphinx 0.9.8-r1065 on the wiki. It's a bit more friendly than the comments of sphinxapi.php

http://sphinxsearch.com/wiki/doku.php?id=php_api_docs

Most of the methods are valid for 0.9.7, but honestly, 0.9.7 is so outdated by 0.9.8 there's hardly a comparison. There's a lot of methods added in 0.9.8.

We haven't got around to updating the actual Sphinx documentation to 0.9.8 yet but it's planned.

[jwksite]

Xorlev,

Have you replicated the Find all posts by user or Find all threads by user in Sphinx then?

I think why they're trying to add a fake keyword is that the search for user's posts/threads normally works by username and not userid. Therefore adding it as a sort field would be impossible as it's not an integer.

I understand that links to users' posts/threads from anywhere other than the actual search.php page would then be able to be turned into one based off the userid instead.. But how do you suggest turning the username into a userid on the actual search.php page? I have only just started looking into Sphinx but I certainly have the capability of learning this really quickly so if you or somebody else could chime in that would be great.

I'm going to be trying to get Sphinx up on my forum in the next few weeks, but I really wanted to hold off until someone comes up with a solid solution to moving "Find all posts/threads" to Sphinx.

On that note.. does this seem to be a good solution? Someone replied in that thread that they had to tweak the search to show actual POSTS and not THREADS, though.

Or would using the userid ultimately be a nicer solution? It'd still require running a query (only when searching right from Advanced Search) to grab the username, though, I assume.. but nothing that would be as awful on MySQL as the current username search.

[Xorlev]

Well, lets see here. What I'd do is grab the userid searched by (either by id, or running the through a query and getting it back).

Note: This uses my knowledge of Sphinx 0.9.8. I'm not sure about the limitations of 0.9.7.

Show all threads by started by user:

PHP Code:

// Other stuff (sorting, etc.)
// Now we grab all threads started by userid.
$cl->SetFilter('postuserid', $userid); 
$cl->Query('', 'thread;threadelta'); 


Here we can then grab the threadids from the matches array.

Show all posts by user:

PHP Code:

// Other stuff (sorting, etc.)
// Now we grab all posts by userid.
$cl->SetFilter('userid', $userid);
$cl->Query('', 'post;postdelta'); 


Grab the postids, retrieve, and show.

Show all threads posted in by user:

PHP Code:

// Other stuff (sorting, etc.)
// Now we grab all posts
$cl->SetFilter('userid', $userid);
$cl->Query('', 'post;postdelta'); 


Now all we have is a bunch of posts. But, what we also have is "threadid" in the attributes of each post. We can collect them, filter duplicates, then retrieve the threads.

Whatever fuzzy matches were done on the username can be done on the user table and returned. The filter value $userid has to be an array anyways, so we can pass it multiple userids we grabbed from the user table. I'm not entirely familiar with vBulletin's classes and such, but I might give it a try to writing in Sphinx 0.9.8 support.

Edit:
I just looked back again and reread this:

# You can only use Sphinx to perform queries that have a full text component. So searches by userid/forumid WITHOUT a key word are not possible. These searches can run on indices though so they shouldn't be an issue.

* Workaround by kmike. "You can emulate the search by user in sphinx by adding a fake unique keyword per each member in the mix (e.g. "_userid_12345"). Searching by this keyword will return all posts by the member with userid 12345."

That's one of the changes in Sphinx 0.9.8. It supports scans, so a blank query returns all results that fall within your filters. Sphinx 0.9.8 will apparently be far easier to implement than 0.9.7.