Meaningful Text Phrase Search on a MySQL Database

[radderfire]

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

 

[gpuerrilla]

Might be just my strange twisted mindset here but I interpret this as like cracking a password by using a database like rainbow tables.

What your doing is manipulating strings, but what I am asking is are you doing this to learn SQL or have you another purpose?

 

[radderfire]

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

 

[gpuerrilla]

Are these products stored in categories or are you just wanting the power of finding results due to text input?

 

[Randomface74]

You want full-text.
https://www.mysqltutorial.org/mysql-full-text-search.aspx

 

[visibleman]

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.

 

[radderfire]

Are these products stored in categories or are you just wanting the power of finding results due to text input?

Just due to text input. I will take a look at full-text.

Thanks Guys

 

[AHarvey]

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

 

[visibleman]

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....

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.