Sphinx Search

[BrandiDup]

Originally Posted by weeno

http://forums.macrumors.com/search.php

Sphinx search with two different search options (match documentation)

Normal = Match All (SPH_MATCH_ALL mode)
Extended = Extended (SPH_MATCH_EXTENDED mode)

both use sphinx.

arn

OK, I'm thoroughly confused now. It looks JUST LIKE vBulletin search. So, the sphinx search is integrated into the actual forums, it's not at all like the google search that you get for your site that brings the search up in a totally different format, right? The search is kind of slow on our site but I tried the google and didn't like it b/c of the format that the results get brought back in. How exactly (in dummy terms) is it different from the search engine built into vbulletin? It looks so much like the standard search, which is very nice, but confusing to me...

[weeno]

Originally Posted by BrandiDup

How exactly (in dummy terms) is it different from the search engine built into vbulletin? It looks so much like the standard search, which is very nice, but confusing to me...

When you initiate a search with normal vBulletin code, it performs a complicated MySQL query to your database. This can be so complicated that it slows down your database and make it unresponsive.

What Sphinx does is run an indexer, which pulls the data from the database and indexes it in a file on the hard drive. So when you search, you access these files. It doesn't tie up the database and is much faster and less resource intensive. The catch is you need to keep updating the files on a regular basis (usually with an automted script running ever 20 minutes or so).

So, from vbulletin's side, you need to modify vb's search.php file, so instead of asking MySQL for search results, it asks Sphinx. The results are then sent back to VB and displayed just like you are used to.

arn

[BrandiDup]

Originally Posted by weeno

When you initiate a search with normal vBulletin code, it performs a complicated MySQL query to your database. This can be so complicated that it slows down your database and make it unresponsive.

What Sphinx does is run an indexer, which pulls the data from the database and indexes it in a file on the hard drive. So when you search, you access these files. It doesn't tie up the database and is much faster and less resource intensive. The catch is you need to keep updating the files on a regular basis (usually with an automted script running ever 20 minutes or so).

So, from vbulletin's side, you need to modify vb's search.php file, so instead of asking MySQL for search results, it asks Sphinx. The results are then sent back to VB and displayed just like you are used to.

arn

OK, that makes sense then. How cool that it integrates so nicely, to be exactly like what the users are used to. My members aren't at all keen on the google search b/c of the way the results display but I think we'll need a better solution than the standard vb search soon. So, this may be worth looking in to.

I did try to skim through most of this very long thread but the original post is gone and some of the thread is sort of confusing to me. So, can you or anyone else tell me if this is a good, stable solution that would be fairly easy for implement?

For the updating that needs to be done every 20 minutes or so, is that done by cron through vbulletin? Or do you enable it somewhere else? And, lastly, does this updating bog the server down? Every 20 minutes seems like a lot of updating but I guess it wouldn't matter if it's not that server intensive.

I'm just trying to figure out a solution that will work for us as we grow.


While we're on this subject though, is the new posts search function as server intensive as normal searches are? Nearly everyone who uses our site uses the new posts feature, so we've got no less than 100 people clicking new posts at the same time, over and over and over again. I was just wondering if this is the same as normal search or if this is something entirely different.

Thank you so much for your help.

[weeno]

Originally Posted by BrandiDup

I did try to skim through most of this very long thread but the original post is gone and some of the thread is sort of confusing to me. So, can you or anyone else tell me if this is a good, stable solution that would be fairly easy for implement?

For the updating that needs to be done every 20 minutes or so, is that done by cron through vbulletin? Or do you enable it somewhere else? And, lastly, does this updating bog the server down? Every 20 minutes seems like a lot of updating but I guess it wouldn't matter if it's not that server intensive.

I'm just trying to figure out a solution that will work for us as we grow.

While we're on this subject though, is the new posts search function as server intensive as normal searches are? Nearly everyone who uses our site uses the new posts feature, so we've got no less than 100 people clicking new posts at the same time, over and over and over again. I was just wondering if this is the same as normal search or if this is something entirely different.

Thank you so much for your help.

It's not that easy a solution to implement. you have to know your way around the linux/unix command line a bit to get the solution working.

Sphinx itself needs to compiled/installed. the config file need to be modified. cron jobs added etc... all doable, but not entirely straightforward esp if you have limited experience with it. myself included, it took me a while to stumble through it myself. All the information is best summarized in these posts:

https://vborg.vbsupport.ru/showpost....&postcount=387
https://vborg.vbsupport.ru/showpost....&postcount=445
https://vborg.vbsupport.ru/showpost....&postcount=450

It does appear to be a stable solution and works very well as you can see.

The New Posts search does not appear as intensive and mine is still running through my regular mysql search and doing fine.

arn

[Spinball]

Originally Posted by BrandiDup

Does anyone have a forum where I can see this type of search in action?

Yesterday Jason very kindly helped me to get Sphinx going on my forums with over 5 million posts at www.avforums.com/forums/search.php
Basically turned a struggling site into a quick one. :up:

[Spinball]

I just deleted the fulltext indexes from post and thread and got the following error when undeleting a thread:

Database error in vBulletin 3.6.8:

Invalid SQL:

SELECT thread.threadid, MATCH(thread.title) AGAINST ('Premium Pack ?190') AS score
FROM thread AS thread

WHERE MATCH(thread.title) AGAINST ('Premium Pack ?190')
AND thread.open <> 10
AND thread.threadid <> 528589

LIMIT 5;

MySQL Error : Can't find FULLTEXT index matching the column list
Error Number : 1191
Date : Wednesday, November 7th 2007 @ 09:50:56 AM
Script : http://www.avforums.com/forums/posti...hread&t=528589
Referrer : http://www.avforums.com/forums/posti...hread&t=528589
IP Address : 82.37.224.75
Username : Stuart Wright
Classname : vB_Database

HELP!

(I recreated the index for now)

[Marco van Herwaarden]

Most likely something to do with Similar Threads. You can try disabling this in your vBulletin Options.

[Spinball]

If it is similar threads and I want to keep the functionality, is there a work around?

[Marco van Herwaarden]

Then you shold not remove the index.

[weeno]

Originally Posted by Marco van Herwaarden

Then you shold not remove the index.

I suppose this answers a question I had about similar threads. I guess similar threads uses full text search. Anyone interested in hacking it to use sphix?

arn