MySQL wrote:
I want searches for News to be on the author, headline and content fields. What is the best way to index and search this?
I would suggest a Fulltext index on headline and content. And then do a search using MATCH() and AGAINST(). For searching on author's names you can use LIKE .
From the manual
``
The `MATCH()' function performs a natural language search for a string against a text collection (a set of one or more columns included in a `FULLTEXT' index). The search string is given as the argument to `AGAINST()'. The search is performed in case-insensitive fashion. For every row in the table, `MATCH()' returns a relevance value, that is, a
similarity measure between the search string and the text in that row in the columns named in the `MATCH()' list.
''
The results are sorted according to relevance, which I think is the best feature of the fulltext search. Oracle provides a thesauraus for Broader term and narrow term also. If you need something like that, you will not find it in MySQL.
There is a workaround for the 3-char minimum search string. You can use a LIKE search query when the search string is less than 3 chars or use the BOOLEAN MODE when searching (availble in MySQL 4.0.1)
Hope that helps
Regards -- Raj Shekhar, System Administrator Media Web India http://www.netphotograph.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
