Improved search

Submitted by Tony on Fri, 2010-02-05 08:29

One of the things that has bugged me for a while has been the slow search. It was always quicker to search the site via google then to use the search box. I had looked at external systems and never really got far enough to implement any of them. A few days ago while reading the news page I came across an article on Sphinx by James at Mozilla Got Data? Need Search? Use Sphinx. So I gave it a try locally and was pleasantly surprised by the improved speed of search.
The hardest part of the implementation was integrating it with Drupal, there are two sphinx modules available but they both need work before becoming usable in Drupal 6. Other then that set up the index is as simple as writing an sql query, being sure to return a unique id, such as the node id, first then the data to be indexed.

Here's the base query I used for Drupal 6.

sql_query				= \
 SELECT n.nid, n.title, n.uid, body, changed, t.tid, c.comment \
 FROM node n \
 INNER JOIN (node_revisions r, term_node t, comments c) \
 ON (n.nid = t.nid and r.nid = n.nid and n.nid = c.nid ) \
 WHERE n.nid BETWEEN $start AND $end AND n.status = 1 \

The query above includes uid and tid which are added as attributes and used to display facets.
Above the search results you will see the facets, which can be used to refine the search results by author or tags.

There may be a little tweaking required over the next few days to ensure the index is updating correctly. Let me know if you notice any issues.

Improved! seems an

Submitted by Hugo on Fri, 2010-02-05 09:38.

Improved! seems an understatement.

Super fast, returning results in around 1 - 2 secs and seems to return pretty relevant matches as well.

I had read that article as well and made a mental bookmark of it. Great to see it action and it seems as good as claimed.

I was doing a disservice to

Submitted by Hugo on Fri, 2010-02-05 09:43.

I was doing a disservice to Sphinx

Quote:

produced 190 results (0.016 secs.)

oh hang on getting slower

Quote:

Your search for background produced 15983 results (0.032 secs.)

Wow, it is a huge difference.

Submitted by Verschwindende on Fri, 2010-02-05 11:34.

Wow, it is a huge difference. I used to do a search and just open another window to occupy the time at another site while waiting.

Yes, much faster indeed!! How

Submitted by CupidsToejam on Fri, 2010-02-05 18:06.

Yes, much faster indeed!! How are the search results queried? Is it my most-recent, or popularity? before if i did a search for divitis, thepineapplehead's post was on the first page. Now I cant even locate it without using google.

Good point Cupid, it looks

Submitted by Tony on Fri, 2010-02-05 21:18.

Good point Cupid, it looks like nothing from the How to section is returned.
I will have to work on my query.
Previously results were weighted on content type and forum. Now they return the results in descending order.

The join in my query was

Submitted by Tony on Sun, 2010-02-07 03:08.

The join in my query was excluding anything that didn't have comments. So I have updated the query and added extra weight to How To topics and added comment counts to the results.

Here's the new query.

sql_query				= \
 	      SELECT n.nid, n.title, n.uid, body, changed, t.tid, td.name, c.comment, nc.comment_count \
              FROM node n \
              INNER JOIN node_revisions r ON n.nid =r.nid \
              INNER JOIN term_node t ON t.nid = n.nid \
	      INNER JOIN term_data td ON t.tid = td.tid \
	      LEFT JOIN comments c ON c.nid = n.nid \
	      LEFT JOIN  node_comment_statistics nc ON nc.nid = n.nid \
              WHERE n.nid BETWEEN $start AND $end AND n.status = 1 \

Perfect, Tony, great work!!

Submitted by CupidsToejam on Mon, 2010-02-08 14:25.

Perfect, Tony, great work!!

Now could you put one of

Submitted by Hugo on Mon, 2010-02-08 22:33.

Now could you put one of those snazzy 'auto suggest as I type' scripts on it and perhaps Ajax in results for that smooth silky feel then...

Ah Hugo, How about the Did

Submitted by Tony on Tue, 2010-02-09 01:16.

Ah Hugo,
How about the Did you mean, feature for misspellings.
Locally I tried the search: backgrou and got did you mean Buckaroo
Unfortunately it came up with backarrow online, more useful but nowhere near as funny.