[Plugin: Search Unleashed] MySQL Fulltext Index – another choice

Hello John – very good plugin (I sent you money!), nicely thought out.

There’s an issue with your choice of MySQL Fulltext index design, though. Your design works OK, but the design also means that the search results are not very granular and sometimes produce “matching” posts that don’t actually match the query at all.

Because you’ve chosen to generate a single Fulltext index that contains 7 columns, Boolean searches become inaccurate. For example, using “IN BOOLEAN MODE” will return a number based upon the number of terms in the AGAINST() clause found in the whole index, rather than a floating-point number representing relevance of the search term to the column being searched upon.

Example:-

MATCH(wp_search_post.post_content) AGAINST (‘Internet Counselling’ IN BOOLEAN MODE)

… will return “2” no matter how relevent the phrase “Internet Counselling” is to the field “post_content”.

Another anomaly is that if a column does NOT contain the search phrase, the query still returns “2”. Example:-

MATCH(wp_search_post.post_excerpt) AGAINST (‘Internet Counselling’ IN BOOLEAN MODE)

… returns “2” (in my experiments), even though the column post_excerpt does not contain the phrase “internet counselling”! This suggests to me that MySQL is searching the whole index to see if the phrase is contained, rather than searching just the part of the Fulltext index that contains “post_excerpt” information.

So, this is an issue with MySQL, not your code as such.

I’m looking at writing an extension to your MySQL plugin which will do the following:-

– creates seven fulltext indexes (one per column)
– only includes the “IN BOOLEAN MODE” tag when there are actually boolean operators in the earch phrase (thus allowing MySQL to generate floating point relevance scores, rather than an integer number representing the number of terms found)
– uses “WITH QUERY EXPANSION” if the search query is very small – in order to generate more accurate results.
– alter the query to only return results that produce a relevance score > 0, otherwise queries seem to return all posts even when they are not relevant to the search query.

This should make search-unleashed produce results that are granular and very muc more accurate that the current single Fulltext index.

Excellent plugin, very impressed!