MySQL Weighted Search

[YorkshireSteve]

Hi All,

 

I'm creating a basic search on a website and want to sort results by relevance. I'm using InnoDB tables so can't use Full-Text searches, but want to order results be relevance.

 

I am searching in title (varchar) and description (longtext), and want items to be sorted based on the number of times a term appears, and where. For example, for every time 'bath' appears in the `title` column, it scores 3 points, whereas for every instance in the `description` column it scores one point. I know there are plenty of ways I can do this with my PHP front-end, but I'm wanting to try this in MySQL (after all, this is part of the data-retrieval process).

 

Any comments are much appreciated!

 

Thanks,

 

Steve

[fenway]

LONGTEXT? You sure you can't use a 65K varchar? TEXT fields have all sorts of performance issues... but I digress.

 

If you need to count more than one appearance of a word per field, you're pooched; you can't do this without a UDF.

[neoform]

Without fulltext you'll have to build your own indexing tables.

 

One table for keywords and the other for linking those keywords to whatever other data you're indexing along with the keyword weight.

 

I suggest you use the porter stemming method for extracting keywords.