Meaningful Text Phrase Search on a MySQL Database

radderfire

radderfire

radderfire

Soldato
Joined
24 Sep 2007
Posts
5,180
Hi Guys

I am looking for some general advice on how to do a meaningful text phrase search on a MySQL database. I have a database full of text descriptions for shopping products, and I want to build a search function against the database that is as useful as possible.

Let's say I have the search term "green mens jumper".

So far, I am splitting the search term up into each word and then running each word against the database, using a wildcard match in the SQL, i.e.

... WHERE ProductName LIKE %SearchWord1% ...

etc

This does produce some helpful results, but use of the wildcard means anything with the term womens in it would also be shown, i.e. it produces irrelevant results.

I have heard of something called full-text search, and producing indexes, is this something that should be done? I am trying to get some level of intelligent search, but this does not seem like an easy task!

Can anyone give me some general advice?

Thanks
 
It's for practical purposes. I have a shopping website that has a list of all product page names in a MySQL database, and I want users to be able to search for a particular product. It is quite a general question, i.e. what is the most powerful way to do text search against a MySQL database. I know how to do the basic SQL queries, but these won't give me particularly good results, so how can I make it better? Full-text search is a concept I have seen, but I don't really know what it is. I am guessing it can create some sort of index from the database to enable better search results. I am just after pointers in the right direction. I have been looking for PHP/MySQL database search scripts but the ones I have found are very basic and therefore can't give good results.

Thanks
 
As @billysielu mentions, Full Text search is what you're after and there's a handful of useful features like fuzzy searching (SOUNDEX() - bit basic).

But using a dedicated search engine, like Sphinx, Lucene or (the newer kid on the block) Elasticsearch, will offer vastly better performance and features at the expense of installing and setting up another service.
 
other ways of doing it would be to stop searching against the text descriptions

You could add a column for search tags for each product or better yet, a series of columns designed to be searched against,

WHERE GENDER = 'MALE'
AND COLOUR = 'GREEN'
AND ITEM = 'JUMPER'

It would also be better for "here are similar items"

It would be a pain in the backside setting it up initially, however, it would more than likely speed up searches and cut down on any strain on the database that wildcard searches would cause
 
AHarvey said:
other ways of doing it would be to stop searching against the text descriptions.
You could add a column for search tags for each product or better yet, a series of columns designed to be searched against....
Click to expand...

If you wanted tags against products then you would have a separate 'tag' table and then a relationship table of tag IDs against product IDs; adding it as separate columns removes any flexibility with the (high) potential for headaches down the line dealing with schema modifications. Plus with decent indexing and DB tuning, performance shouldn't be an issue with JOIN's and separate tables.

And whilst online shop platforms do use tags/tagging (particularly for filtering results), most do deploy text searching against descriptions, product title/subtitles etc albeit usually using a search engine or better suited DB platforms.

But i assume this is coursework/learning as there's plenty of decent off-the-shelf shopping platforms to use rather than trying to reinvent the wheel.
 
You must log in or register to reply here.
Back
Top Bottom