Include Other CCK fields & also Taxonomy (probably Content Taxonomy Support)

I'm definitely working on the weight thing. Because of the nature of increasing weight in FULLTEXT searches, I have to devise a way to do it directly in the query. This is because the entirety of the module's relevance scoring is done directly in MATCH queries, and no code is involved in actually determining relevancy. Being able to weigh certain columns will mean redoing how database columns are indexed in the database.

Currently, both columns are indexed together:

Column1 | Column2
     Index both

To add weight, we need to index columns separately plus one together:

Column1 | Column2
Index1      Index2
     Index both

Each MATCH query that is added to the $selects array is against a specific index on database columns. Currently, columns in any given table are indexed together in a single index as shown above. To add weights columns need to be indexed both together and separately. This will allow us to get a relevancy score on individual fields rather than all fields in a table, thus allowing us to manipulate the relevance score. So, with individual columns being indexed we can get a query on a specific field that looks like:
SELECT MATCH (column) AGAINST (text) AS score ORDER BY (score * weight).

That's just the part of query that will be for weighing a field. Notice that the score is multiplied by a user set weight. What I'd like to ultimately to is provide options in the views argument for setting the weight of a field like this. So, getting the weight change set up will take a little bit of reorganizing how indexes are made, but it shouldn't be difficult. Database columns are indexed in hook_cron(), so that just needs to be changed to set multiple indexes and probably store the index names for use in the view. The views argument will then need to be rewritten a little bit to add a MATCH AGAINST statement for each field of a table plus all fields of a table, plus a weight for the weighted field (probably just title).

Separately, I would like to get taxonomy terms involved. Taxonomy was partially integrated into the original version of Similar entries, and it's certainly on the todo list. But what's important to me is to stay in keeping with the Views way and not add features where they wouldn't normally be seen, though I do think Similar is in a unique position because of the way FULLTEXT searches work.

As for CCK fields, currently the module only indexes CCK text fields, simply because I haven't tested it on other types of fields. I'll have to run some tests on relevancy when using different types of fields, but all you need to do to change the module code is change if ($info['type'] == 'text') { to add an && $info['type'] == 'desired_type' (I think it's in similar_cron()?), run cron, and it should pick up the newly allowed type of field. Now that I think of it, field type can even be offered as an option in the argument if we want.

I'll certainly take a look at these two items. I think if I'm already going to be digging into the argument query to add weights to individual database columns I might as well take the opportunity to add taxonomy terms into the query rather than rewriting it twice. I get a lot done on the weekends and this might be one of those items.

FYI,
I came across an article that talks about a boolean mode:
http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

this comment mentions a way to include weights:
Posted by Brad Satoris on December 13 2004 9:14pm [Delete] [Edit]
Boolean searching has two deficiencies: 1) results are not sorted by relevance and; 2) no method by which to weigh certain columns. There is a way around both of these problems. For example, if I have a table of articles and want to weigh the title more heavily than the text, I can do the following:

SELECT *, ( (1.3 * (MATCH(title) AGAINST ('+term +term2' IN BOOLEAN MODE))) + (0.6 * (MATCH(text) AGAINST ('+term +term2' IN BOOLEAN MODE))) ) AS relevance FROM [table_name] WHERE ( MATCH(title,text) AGAINST ('+term +term2' IN BOOLEAN MODE) ) HAVING relevance > 0 ORDER BY relevance DESC;

Here we artificially manipulate the relevancy score to give title more weight by multiplying by the constant 1.3. In the above query, it doesn't seem to matter whether I have 3 fulltext indexes or just one comprising the title and text columns. From my testing, the results appear to be the same.

results are not sorted by relevance

That is, results are not automatically sorted by relevance, but they certainly can be sorted otherwise the search would be worthless without knowing which result is the most relevant. So this is no big issue.

Good information to know that results were the same whether each column was indexed separately or whether they were indexed together. With the database structure in Drupal 7 I can only really manage to index one column at a time since they're mostly in separate tables anyways.

Thanks for that query. I've never seen it done that way. I particularly like that it uses math so we could theoretically make the weight of each field customizable. This happens to be a much better method than the one I previously saw - indexing fields together and separately and performing extra MATCHes on the fields from which you want an increased score.

Currently, 7.x-2.x does support fields in its queries in both versions. Also, it increases the relevance of the words in the current node's title by using boolean operators if the query is being run in boolean mode (there's an option for enabling or disabling boolean mode searches). But I certainly would like to leverage your example to increase the relevance of destination nodes' titles or other fields as well.

Patches always welcome : ) but I'll probably get a patch together for this today anyways.

Here's a patch I put together to support better relevance in boolean mode searches. This patch is for 7.x-2.x, though, as all new development is being done in 7.x and backported to 6.x branches. I've been able to get a lot of really good work done on the 7.x-2.x branch and there's a lot to port to 6.x-2.x. 7.x has several new options in the Similar Entries: Nid argument handler that allow the user to switch between boolean and normal mode, increase or decrease the relevance of words or phrases in the current node's title, body, or fields, increase or decrease the relevance of title and body/fields in destination nodes, and limit which fields - defined by the field module - should be included in the query.

I used the query you posted in #5, but it's altered to allow the user to configure the math and thus the relevance. Thanks!

Update: We did some testing on boolean mode vs normal mode queries. The simple fact - it seems - is that boolean mode is not nearly as affective for our purposes. This is because boolean mode searches do not discount common words whereas normal searches do not count words that appear in > 50% of entries towards the similarity score. So, I'll probably leave in the boolean mode option, but it will be disabled by default. We can still increase the relevance of node titles vs node bodies by using a version of the query in #5, and I'll be putting together a patch for 6.x-2.x and 7.x-2.x to do this.

I ported this and another patch to Drupal 6. That version now has options for using BOOLEAN MODE and including CCK fields, though I think that area may still be a bit buggy. It does need some good testing and it has been committed and should be in the development snapshot.

Issue queue triage, closing all 6.x issues. If this one is still a problem for Drupal 7, please re-open.