0

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.

Improve this question

3 Answers 3

Reset to default
2

Make use of Full Text Indexing

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

haven't used it before, but it seems this is the best solution so far. thanks.
1

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.

Improve this answer

Comments

1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.