Enhanced Search for an Online shop

How do the big guys do it?

[jecasc]

One of the most essential things in any online shop is the search box. My Online Shop is based on a MySql Database. The search is simple and not very smart. You enter a serch term. The search term is then looked up in the product name and product description field of the database.

However:

- the result is not sorted by relevancy
- if a word is not spelled correctly no match is found.

Does anybody have an idea how to approach this isues in MySql. How could I check for example the relevancy of a search result. How could I suggest alternative spellings. I would appreciate any suggestions that would lead me onto the right track or feedback if you have already implemented something like this.

[FalseDawn]

You could try using a "soundex" search, which would help with mis-spellings. For each product, you could store a list of soundex'ed keywords for example.

A simple "relevancy" test could be the number of times the search string is found in the description for example - don't know if there are any SQL commands for this - doubt it, but it would be fairly easy to store results in an array and use server side code to do this.
For more complex relevancy matching, you'd need to do some searching for appropriate algorithms

[jezzer300]

If your using MyISAM tables you can set the column you want to search as fulltext. Then search using a special fulltext SELECT.

[dev.mysql.com...]

Here's there example query which order by relevance:

SELECT id, body, MATCH (title,body) AGAINST
('Security implications of running MySQL as root') AS score
FROM articles WHERE MATCH (title,body) AGAINST
('Security implications of running MySQL as root');

[Easy_Coder]

jecasc, I've got the same issue. Here's the approach I'm taking.

What I'm doing is building what I call a Search Adapter that collects some result metrics.

- What did the user search for
- Was the search for information or a product
- How many results were delivered
- What result actually got clicked
- If a result was clicked then where was it in the resultset

I'm also collecting and indexing user product ratings in addition to indexing my non-product information pages.

So now I have a bunch of stuff that can go into a decision making process other then just text pattern matching.

[hgolov]

Question: If I convert the field to fulltext type, would it slow down retrieval time for my entire store- - I have over four thousand records?

[aspdaddy]

> if a word is not spelled correctly no match is found.

SQLs soundex is poor. It takes a lot re-writing to be any use, there is a version in "the gurus guide to transact sql" that does the trick, once yopu undersand how it works you can customise it for your sites product names.