0

I have a table as shown below. A user can search this table on the basis of Interests. For example, user can search “Carl to”. Then it should return the first 2 emails, because we have 2 similar sounding words like Carl, Karl and to, two. At the same time, the search is based on multiple words and not just a single word. Hence it has to be FULL TEXT INDEXING.

But I don’t know how this will be possible. How can I make a query that satisfies both FULL TEXT INDEX and similar sound words?

Any help in this regard is highly appreciated.

Id  |   email           |   Interests
-------------------------------------------
1   | [email protected]     | travel to Carl
2   | [email protected]     | journey two Karl
3   | [email protected]   | float in the ocean
Improve this question
7
  • I don't think this is possible with mysql. Fulltext index can splot text into words, but cannot do sounds like search. Soundex function can do sounds like search, but cannot detect separate words. Mysql is a relational database product, not a text search one. You may need to look into different text search products if this is a true requirement for you. Commented Nov 2, 2024 at 14:53
  • Yes! I understand that two things cannot be done simultaneously. I also understand that it is a common logic behind searches. Any blog will always have a FULL TEXT SEARCH and also users can make spelling mistakes, so SOUNDEX is also essential. However, I am just thinking of alternative ways like a UNION of two queries or otherwise. Commented Nov 2, 2024 at 14:59
  • 1
    Let me emphasize again: you are trying to use the wrong product for what you try to achieve. Commented Nov 2, 2024 at 15:00
  • That is the whole purpose of my question. The product is not wrong but perhaps my method or approach is wrong. Spelling mistakes are common but it doesn’t prevent users from searching - right. It is also common to search for something from 500 words - right. Without these two things a search is useless. I just don't know how to solve this. Commented Nov 2, 2024 at 15:16
  • 1
    Not with mysql. You need a text search product. Commented Nov 2, 2024 at 15:27
Related questions
1458
How can I output MySQL query results in CSV format?
921
MySQL: Large VARCHAR vs. TEXT?
909
How can I do a FULL OUTER JOIN in MySQL?
Load 5 more related questions Show fewer related questions

0

Reset to default

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.