fulltext searching & relevancy

antcook

Hi,

We're trying to implement a search facility for our product catalog which is stored using MySQL. I have tried LIKE queries, which are obviously not very good, and I have also tried FULLTEXT searching using MySQL 3.xx. The problem is that the FULLTEXT implementation in MySQL 3.xx isn't very good and seems to struggle with the fields & queries I'm doing. So I have concluded that I need something else.

Standard site search scripts aren't any good, as I only need to search titles & descriptions in the database. I need a good relevancy percentage too, to rank the results.

Anyone got any ideas on how to solve this? Are there any relevancy PHP code examples out there? Or maybe indexing software modules?

thanks in advance

anthony

Bunkermaster

when you insert in your table split description into words, store words in a table with a unique ID, store in a link table the word IDs for the description and when you do a search just split words in search query, select every product that has the words you want in it's desc et voila!
just thought about that solution, it mau not be the best solution ever...

my 2/100 US$

antcook

Okay, that seems like a way to do the indexing, but what about a relevancy ranking?

Bunkermaster

good question thanks for asking
/me walks away silently avoiding to look back

zmola

i've been working on a little something that you may be interested in, so if you want, pm me.

just search on the letter a at first so you can see some other words to search on and get a feel for how it works. the db is a junk db for testing.

it deals with one table, a number of columns within that table, you can set the relevancy weight, set which column is shown as a link, if any, allow up to x chars for like as part of queries (i currently have it set to 5), it also uses fulltext, highlights, blah blah blah...

not sure if i'm going to give it up under gpl, qpl, charge for it, or what. it was a little project i started by first using scraps of freebie code and then putting it all together with customizations in the hopes of emulating a google search on my db and learning php in the process. now that little project is about 15 pages of code; big for me.

to do boolean seaches, use: and, or, not, and double quotes for phrases. all other chars are stripped. anyhow... i'm no php whiz... yet... but hopefully i'm a little closer to that goal. if you are interested, feel free to pm me.

killerbishop

FULLTEXT searches automatically search based on relevancy and are ordered automatically in relevant order.

If you want to know what the relevance of a search row was, you need to SELECT the physical MATCH statement, so:

SELECT MATCH(content) AGAINST('example') as relevance FROM example_table WHERE MATCH(content) AGAINST('example');

MySQL will return a row like:

| relevance: |

| 0.342161 |

This number is between 0 and 5, with 5 being more relevant. It is based on the relevance of the match against all other documents found relatively in the search.

zmola

yep, plus i allow like in my queries up to 5 chars. an example using mysql 3.23:

SELECT blah1, blah2, .4(LENGTH(blah1) - LENGTH(REPLACE(LOWER(blah1),LOWER('test'),''))) / LENGTH('test') + .4(LENGTH(blah2) - LENGTH(REPLACE(LOWER(blah2),LOWER('test'),''))) / LENGTH('test') + MATCH (blah1, blah2) AGAINST ('foobar') AS relevance FROM table WHERE MATCH (blah1, blah2) AGAINST ('foobar')>-1000 AND (blah1 LIKE '%test%' OR blah2 LIKE '%test%') HAVING relevance>0 ORDER BY relevance DESC;

but i set a var so i can turn it off in the event my db gets large and performance suffers. all this stuff is so cool :😉: and i used to think alternating table cell color was hard ::hee hee::

antcook

I know about fulltext MySQL searches, but they're not very good in 3.23 - no wildcard matching.
I just read that SQL zmola posted:

But there is no way I'm understanding that at 17.30pm here!!! It sure seems clever, but can someone explain it a bit more for me?

thanks in advance

anthony

zmola

sure!

the formula

.4(LENGTH(blah1) - LENGTH(REPLACE(LOWER(blah1),LOWER('test'),''))) / LENGTH('test') + .4(LENGTH(blah2) - LENGTH(REPLACE(LOWER(blah2),LOWER('test'),''))) / LENGTH('test')

assigns a relevance to wild card matches. basically, in the above formula, each wild card match is assinged .4.

then

MATCH (blah1, blah2) AGAINST ('foobar') AS relevance FROM table WHERE MATCH (blah1, blah2) AGAINST ('foobar')>-1000

adds on the relevance returned from match against, where -1000, although excessive, makes sure you don't block out common words (while counting zero relevance from common words) from searches using wild cards.

try searching only on the word 'the' at the above link. each time there is a wild card match to 'the', the relevance is increased. at my site i have .2 instead of .4 so as to not given to much relevance to wild card matches.

also, at my site i have set the wild card match allowance to be up to 5 letters only. try searching on the wild card 'the' and the word 'distance' in the same search and see the relevance, i.e., rank ouputs.

the wild card 'the' will contribute (.2 * each 'the' occurrence) at my site, and the word 'distance' will contribute whatever match against says.

also, if you search only on a common word with length over 5 letters such as 'probably', then no results are returned.

however, if you search on 'the' and 'probably', you will see that the wild card 'the' contributes .2 as described above, but the common word 'probably', although it now shows up in the search results, contributes zero to the relevance.

i have this all working in mysql 3.23. hope that helps.