按标签搜索域名？

Question

I do have 100k domains with with their related tags.

I want search domains by their tags. for example google.com domain is with search,google,searchengine,engine,web,reference tags

bing.com also with search,bing,searchengine,engine,web like this I have upto 100k domains with their related tags.

Criteria 1

If I search with tags say search,google,searchengine,engine,web,reference then the both domains google.com and bing.com should display in final result.

Criteria 2

If I search with tags search,searchengine,engine,web then also both domains google.com and bing.com should appear in results

Criteria 3

If I search with tags search,searchengine then also both domains should be displayed.

Criteria 4

If only tag search then also both domains need to be display.

Criteria 5

How do I prioritize result with its tag say if I search with tags search,google,searchengine,engine,web,reference then google.com should come first and bing.com come second

Finally to achieve all these results how should I design my table and how I should query to table?

Thanks

Answer 1

You need to have at least two columns domain_name varchar(400), tags text -- you need to make sure all tags are comma separated.

Now, you make tags an index of type FULLTEXT to do full text search. See here for description. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

On a quick Google, there are many posts on utilizing MySQL text search to get relevant results sorted by relevance. (Hope this is what you wanted).

One such example is here http://www.pui.ch/phred/archives/2005/05/tags-with-mysql-fulltext.html It shows various ways to handle tag searches.