Sphinx Search

[orban]

I will be releasing an updated version of the vBulletin Sphinx integration for 3.6 and 3.7 without file modification (two plugins, two file uploads), later today. Contains copy paste code from search.php though (about 300 lines) but I do think it's worth it and necessary. Currently working on a few small problems (similar threads, tags, thread prefixes).

If somebody has a 3.7 install and integrated these with Sphinx and can help me out, I'd be very grateful. In more detail I'd like to know how thread prefixes and tags are stored in the thread table (I haven't set up a 3.7 test environment yet).

[amcd]

Welcome back, orban.

[orban]

Hello amcd!

Anyway, slowly getting back in business, updated the first post in this thread... just sharing what I have at the moment. Maybe I can get some feedback

It's kind of messy but yeah

As usual if you break your server it ain't my fault

[mack101]

Originally Posted by ivanp

My guess is you were using --rotate switch, which temporarily creates copy of indexes. When it finishes, it replaces existing indexes. This is normal behavior.

Actually it was just the 'indexer --config sphinx.conf --all' command, same as I had run before.

Going to try again today.

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

Sorry, I think I understand what is going on now.. I just don't have enough physical disk to generate the temp files and then the indexes. Doh!

I will move my indexes from the test site to the production server then run an update on that.

[Simetrical]

Originally Posted by ivanp

Not really sure if indexes should be dropped.

I think other functions such is "Similar threads" depend on it. Should be checked.

If you don't drop the indexes, you'll lose a good part of the benefit of switching to Sphinx (although you'll keep most of it, it's true). The indexes are very large and keeping them up-to-date will add to server load unnecessarily. Although not half as much as the locks that conducting the actual searches takes out.

[scanlover]

Thanks Orban!

I just tried your version 0.1 on my 3.70 board
Tag search seems ok, personally I dont use thread prefixes and similar threads on my board..
Other than that, all that you said should work seems to be working fine.

A few points:
1) if no results returned, I get some extra text at the end of the error message
2) should there be a way to set weights for title/postbody?
3) how can we confirm that the search cache is being used?

[orban]

1) Change line 514 to "$errors[] = array('searchnoresults', '');"
2) Add "$cl->SetFieldWeights(array('title' => 100, 'pagetext' => 10));" after line 410 and change those values to your liking (see here http://sphinxsearch.com/doc.html#weighting)
3) Search cache? It's definitely not using vB built-in search caching because that one is horribly ++++ed up

Tag search is not yet enabled but luckily for us Sphinx supports multi-valued attributes (http://sphinxsearch.com/doc.html#mva) so we can store all tags for every thread. As soon as I get my hands on a 3.7 installation I can implement that.

Prefix search... I can't believe how ++++ing retarded vB is. Prefixes don't get an id, they are just stored as strings (there is no unique integer id in the table). Sphinx doesn't support string attributes. I'm a bit at a loss here. Possibly a very simple hashing function... but it'd have to be implemented in MySQL... what were they thinking

[scanlover]

thanks again.. works great.

Actually vB tags are implemented at thread level, so I don't really see the need to implement sphinxsearch on that unless someone does a non-trivial hack to make tags operate at post level.

What will be great would be to add a new advanced search form that caters specially to sphinxsearch. The old advanced search form can work alongside it (it can be used for title-search only queries).

[orban]

That's a bit beyond what I'm trying to accomplish here but if somebody wants to give it a go

[NickCat]

Thanks for the great work orban to keep this going!!!

I just installed .1 and everything seems to be working great.

I am getting an error on the redirect page before the results pop though, nothing bad, just informational.

Warning: in_array() [function.in-array]: Wrong datatype for second argument in /search_sphinx.php on line 160

I am running vb 368p2.

I was trying to hold out upgrading to 3.6.10 until I'm sure they aren't going to .11 in a week! I'm had vb long enough to know better!

--------------- Added 06 May 2008 at 01:48 ---------------

I guess I spoke too soon.

I upgraded to 3.6.10, to try to alleviate any other issues.

Having a few other problems... and I'm not sure what's going on.

1) For some reason my updates on the deltas aren't working. It runs, but never finds anything to index. And there are definitly new posts and threads to index.

The result looks like this:

Sphinx 0.9.8-rc2 (r1234)
Copyright (c) 2001-2008, Andrew Aksyonoff

using config file '/etc/sphinx.conf'...
indexing index 'postdelta'...
collected 0 docs, 0.0 MB
total 0 docs, 0 bytes
total 0.011 sec, 0.00 bytes/sec, 0.00 docs/sec
indexing index 'threaddelta'...
collected 0 docs, 0.0 MB
total 0 docs, 0 bytes
total 0.010 sec, 0.00 bytes/sec, 0.00 docs/sec
rotating indices: succesfully sent SIGHUP to searchd (pid=22708).

The command I'm running is:

indexer --config /etc/sphinx.conf --rotate postdelta threaddelta

Am I wrong in thinking the deltas are still called postdelta and threaddelta?

2) I'm getting an occasional error when people are searching:

Database error in vBulletin 3.6.10:

Invalid SQL:

                INSERT INTO search
                    (userid, titleonly, ipaddress, personal, query, searchuser, forumchoice, /*prefixchoice,*/ sortby, sortorder, searchtime, showposts, orderedids, dateline, searchterms, displayterms, searchhash, completed)
                VALUES
                    (78408,
                    1,
                    '71.119.247.53',
                    1,
                    'events',
                    '',
                    '20,102,133',
                    'lastpost',
                    'DESC',
                    0.01146, 0,
                    '1507990,1202618,1497231,1424353,1425729,1370173,1327330,1327625,1275317,1280585,1053389,1247121,1220128,1173630,1187708,1191528,1161131,1114547,1078119,1024452,947834,887728,855358,853807,816227,783190,718582,649979,649714,644579,593661,565031,556141,539177,532216,501639,476210,459248,426142,424279,369567,339477,300242,269273,252339,204641,179446,128824,109786,19493,19998',
                    1210052178,
                    'a:24:{s:5:\"query\";s:6:\"events\";s:10:\"searchuser\";s:0:\"\";s:9:\"exactname\";i:1;s:11:\"starteronly\";i:0;s:11:\"forumchoice\";a:1:{i:0;s:2:\"20\";}s:11:\"childforums\";i:1;s:9:\"titleonly\";i:1;s:9:\"showposts\";i:0;s:10:\"searchdate\";s:1:\"0\";s:11:\"beforeafter\";s:5:\"after\";s:6:\"sortby\";s:8:\"lastpost\";s:9:\"sortorder\";s:10:\"descending\";s:9:\"replyless\";i:0;s:10:\"replylimit\";i:0;s:14:\"searchthreadid\";i:0;s:9:\"saveprefs\";i:1;s:11:\"quicksearch\";i:0;s:10:\"searchtype\";i:0;s:7:\"exclude\";s:0:\"\";s:7:\"nocache\";i:0;s:4:\"ajax\";i:0;s:9:\"imagehash\";s:0:\"\";s:10:\"imagestamp\";s:0:\"\";s:6:\"userid\";i:0;}',
                    'a:8:{s:5:\"words\";a:1:{i:0;s:6:\"events\";}s:9:\"highlight\";a:1:{i:0;s:6:\"events\";}s:6:\"common\";a:0:{}s:5:\"users\";a:0:{}s:6:\"forums\";a:1:{i:20;i:20;}s:8:\"prefixes\";a:0:{}s:3:\"tag\";s:0:\"\";s:7:\"options\";a:3:{s:11:\"starteronly\";i:0;s:11:\"childforums\";i:1;s:6:\"action\";s:7:\"process\";}}',
                    '0b537c34de784ac889ce7bca2e26adeb',
                    1)
                ### SAVE ORDERED IDS TO SEARCH CACHE ###;

MySQL Error  : Duplicate entry '0b537c34de784ac889ce7bca2e26adeb-lastpost-DESC' for key 2 Error Number : 1062
Date         : Tuesday, May 6th 2008 @ 01:36:18 AM
Script       : http://forums.nasioc.com/forums/search.php?do=process
Referrer     : http://forums.nasioc.com/forums/search.php?do=process
IP Address   : 71.119.247.53
Username     : psychoskip
Classname    : vB_Database

Any ideas... cause I'm lost here...

The database error was happening on both 3.6.8 and 3.6.10. The search is still working most of the time, but this seems to be having an issue thinking it's inserting a duplicate key into the table.

UPDATE: I reverted back to the original sphinx setup I was using for now. A couple of things I will have to say I noticed immediatly between the two version. The older version took significantly longer to index and took up more drive space, but the results seem to return much faster.