Mysql Search for Domain Names

Question

Hi

I have a table full of domain names. I'd like to do a search that returns some kind of relevancy results on it. My problem, is that if I do a search for "cool" I want it to return "cooldomain.com", which a fulltext search, unless I'm doing it wrong, will not.

Are there any fulltext options I'm unaware of that will accomplish this? If not, how would I go about doing it?

Answer 1

Good old WHERE domain LIKE '%cool%' will match, but doesn't return "relevancy" -- I'm not sure how you'd define that.

Answer 2

Try something like

SELECT * FROM domains WHERE domain_name LIKE 'cool%';

Answer 3

I'd use LIKE here, fulltext search is for matching against full words or expressions, query expansion, etc. And I think the * operator can only be used as a suffix when using MATCH, so you'll miss imcool.com...

I think you'll have to gather more information to make a relevancy sorting.

Edit: If you want to use an index, you can also store the words of the domain in another column, and use the power of fulltext search on this one...

Answer 4

You will need to set a FULLTEXT index to your table and then do something like this:

SELECT *, MATCH(domain_name) AGAINST ('{search term here}' IN BOOLEAN MODE) as Relevance FROM domains WHERE MATCH (domain_name) AGAINST('{search term here}' IN BOOLEAN MODE) ORDER BY Relevance DESC