CODEWITHSUNDEEP
phpsearchmysqlfull-text-search
Ervin
Ervin

Reputation: 2452

MySQL multiple keyword search algorithm

Can anyone give me ideas or recommendations on how to build a multiple search algorithm?

For example, I have a products table in my MySQL database, and I'd like to be able to search for multiple keywords in the title and description columns. These keywords could contain multiple words, for example: "blue electric bosch toaster machine".

What would be the optimal method for accomplishing this?

So far I was thinking about: 1. Using the LIKE "%$keywords%" in MySQL 2. Spliting the keywords based on whitespace, and searching on each individual word, but I think it's best that I search for the whole keyword.

Upvotes: 0

Views: 3937

Answers (3)

Adam
Adam

Reputation: 1108

By using LIKE with percentages on the start and end, you'll have a wildcard either side of your keyword. So this should get you what you want.

To search for the whole phrase:

WHERE (Title LIKE "%blue electric bosch toaster machine%" OR Body LIKE "%blue electric bosch toaster machine%")

Or to search for each keyword individually (will return more results):

WHERE (Title LIKE "%blue%" OR Title LIKE "%electric%" OR Title LIKE "%bosch%" OR Title LIKE "%toaster%" OR Title LIKE "%machine%" OR Body LIKE "%blue%" OR Body LIKE "%electric%" OR Body LIKE "%bosch%" OR Body LIKE "%toaster%" OR Body LIKE "%machine%")

If you're searching on a massive dataset and common methods like LIKE are too slow then I recommend using something like Sphinx - it's VERY fast at full text searching.

Upvotes: 1

Thibault Witzig
Thibault Witzig

Reputation: 2200

Use OR between keywords to get every record that matches at least one keyword.

Then programmatically set a value to each match. For example, give higher value to the first match of each keyword, so that records that match multiple keywords have precedence over those that match the same word multiple times.

Upvotes: 1

xxx
xxx

Reputation: 36

Make use of Full Text Indexing

Upvotes: 2

Related Questions