YorkshireSteve Posted May 15, 2008 Share Posted May 15, 2008 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted May 15, 2008 Share Posted May 15, 2008 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. Quote Link to comment Share on other sites More sharing options...
neoform Posted May 15, 2008 Share Posted May 15, 2008 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.