Sphinx Search

[bmanzzz]

can some one please provide a step by step how to install Sphinx Search and configuring
and then how to use it with Vbulletin

[ALanJay]

Hi,

Well I now have a system that appears to work but for the vBulletin database always gives no results but for my own database gives the expected results.

Between 0.9.6 and 0.9.7 have there been many changes to the sphinx.conf it is the only thing that I can think might be causing the issue unless anyone has any other ideas?

[amcd]

Originally Posted by bmanzzz

can some one please provide a step by step how to install Sphinx Search and configuring
and then how to use it with Vbulletin

https://vborg.vbsupport.ru/showpost.php?p=1104866

This link is given right at the top of the first post of this thread. This is the most comprehensive guide so far. If this is not enough, then you have to read through the whole thread.

ALanJay, I am running sphinx without problems on FreeBSD amd64. If you have any specific questions about versions etc, maybe I can help.

[ALanJay]

Originally Posted by amcd

ALanJay, I am running sphinx without problems on FreeBSD amd64. If you have any specific questions about versions etc, maybe I can help.

Thanks amcd,

Well most of my FreeBSD issues have been solved - compilation and running all see fine.

I have changed sphinxapi.php to include the sugested:

function unpack31($f,$s)
{
$arr=unpack($f,$s);
foreach($arr as $k=>$v) {
$b = sprintf("%b", $v);
if(strlen($b) == 64){
$arr[$k]=bindec(substr($b, 33));
}
}
return $arr;
}


I have made the changes to sphix.conf for I think for the changes to the column settings ie

sql_group_column = forumid
sql_group_column = threadid
sql_group_column = userid
sql_group_column = postuserid
sql_date_column = dateline
sql_query_post =

I think the SQL stuff is unchanged from 0.9.6 to 0.9.7 and still have for the Post Index:

        sql_query_pre           = REPLACE INTO spy_forum.sph_counter SELECT 1, MAX(postid) FROM post
        sql_query_range         = SELECT MIN(postid), MAX(postid) FROM post
        sql_range_step          = 1000
        sql_query = \
                SELECT postid, forumid, post.threadid as threadid, IF(post.userid=0,99999999,post.userid) AS userid, IF(postuserid=0,99999999,postuserid) AS postuserid, p
ost.title, pagetext, post.dateline \
                FROM post \
                INNER JOIN thread AS thread ON(thread.threadid = post.threadid) \
                WHERE post.visible = 1 AND postid >= $start AND postid <= $end \
                        AND postid <= ( SELECT max_doc_id FROM spy_forum.sph_counter WHERE counter_id = 1 );

and the delta

        sql_query_pre           =
        sql_query_range         = SELECT ( SELECT max_doc_id FROM spy_forum.sph_counter WHERE counter_id = 1 ), MAX(postid) FROM post
        sql_range_step          = 1000
        sql_query = \
                SELECT postid, forumid, post.threadid as threadid, IF(post.userid=0,99999999,post.userid) AS userid, IF(postuserid=0,99999999,postuserid) AS postuserid, p
ost.title, pagetext, post.dateline \
                FROM post \
                INNER JOIN thread AS thread ON(thread.threadid = post.threadid) \
                WHERE post.visible = 1 AND postid >= $start AND postid <= $end \
                        AND postid > ( SELECT max_doc_id FROM spy_forum.sph_counter WHERE counter_id = 1 );

index DSFullTextPostIndex
{
type = distributed
local = DSPostIndex
local = DSPostIndexDELTA
}


it all seems to work ok when I create the indexes from scratch but the test.php and vB search.php always give 0 results.

ie

php ./xx-test-search.php -i DSFullTextPostIndex "digital tv"
Query 'digital tv ' retrieved 0 of 0 matches in 0.000 sec.
Query stats:
'digital' found 0 times in 0 documents
'tv' found 0 times in 0 documents

yet when I use my internal database it all works fine:

php ./xx-test-search.php -i DSramsIndex "digital tv"
Query 'digital tv ' retrieved 1000 of 1729 matches in 0.005 sec.
Query stats:
'digital' found 20655 times in 4243 documents
'tv' found 24047 times in 5666 documents
Matches:
1. doc_id=4744, weight=208, date=2002-01-20 22:44:53
2. doc_id=4868, weight=208, date=2002-01-31 20:01:25

This would imply some sort of error with the creating of the vBulletin index in differences between the 3.0 stream I was previously using and the 3.6 stream of the new test site?

Any thoughts?

[amcd]

I am using sphinx 0.9.7 rc1. I have not made the edit for unpack31.

Why do you write spy_forum.sph_counter all the time? Is the counter in a different DB? I have the counter in the same DB.

One thing I noticed is that the command line search does not return any results from compound indexes.

search -c /usr/local/etc/sphinx.conf --index 'postmain' something
and
search -c /usr/local/etc/sphinx.conf --index 'postdelta' something
both work.

but
search -c /usr/local/etc/sphinx.conf --index 'post' something
returns zero results.

Maybe you are facing the same issue with the test script.

For what it is worth, I can send you my files if you want.

[orban]

Originally Posted by amcd

One thing I noticed is that the command line search does not return any results from compound indexes.

Confirmed...I don't know if that is a bug or a feature.

[ALanJay]

Well that explains something - having done some more testing I can get results from the test programme now from ThreadIndex but not PostIndex which is very peculiar.

And this follows through on the actual vb search.php in that if you "search titmes only" it seems to work but "search entire posts"

One of my colleages was kind enough to compare the actual mySQL and the results from the different 3.0 and 3.6 databases and we discovered that there was a difference. It appears that after the upgrade (to 3.6) the index doesn't seem to be being correctly read so an explicit inclusion of - USE INDEX (threadid) - we thoguht might work:

                SELECT postid, forumid, post.threadid as threadid, IF(post.userid=0,99999999,post.userid) AS userid, IF(postuserid=0,99999999,postuserid) AS postuserid, post.
title, pagetext, post.dateline \
                FROM post \
                USE INDEX (threadid) \
                INNER JOIN thread AS thread ON(thread.threadid = post.threadid) \
                WHERE post.visible = 1 AND postid >= $start AND postid <= $end \
                        AND postid > ( SELECT max_doc_id FROM spy_forum.sph_counter WHERE counter_id = 1 );

Except that doesn't work when creating the sphinx index but does when using mysql directly.

[Rayn21]

Originally Posted 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.

Are further modifications to orban's code required to get this working?

It seems that my modified search.php is still using the VB search engine for some queries. (those without keywords)

[orban]

Yes mine doesn't support that.

[rix]

thanks orban for the script, I'm loving it!.

I used the search.php and applied orban's patch but I only get the result when I choose the option xx month ago or something but not "Any Date". The debug returns the following for "Any Date"

Query '' retrieved 0 of 0 matches in 0.027 sec.
Query stats:
'kereta' found 5277 times in 3731 documents

While the same keyword from "Yesterday" returns

Query '' retrieved 4 of 4 matches in 0.005 sec.
Query stats:
'kereta' found 5277 times in 3731 documents

I'm guessing something wrong with the php but not sure which script.