How do you add a column to a fulltext search in MySQL to indicate which words were included in the search?
+1
A:
You mean query or index definition? For query it would be something like this:
WHERE
MATCH (colA, colB) AGAINST ('+words* +you* +search*' IN BOOLEAN MODE)
Michal Dymel
2010-01-18 14:47:10
Thank you for your help. The SELECT statement is really where I need the guidance. For example, if this is the query:select AlbumID, Title from Albumswhere match(AlbumDesc)against('albums')unionselect AlbumID, Title from Albumswhere match(AlbumDesc)against('albums' with query expansion)unionselect AlbumID, Title from Albumswhere match(AlbumDesc)against('studio')unionselect AlbumID, Title from Albumswhere match(AlbumDesc)against('studio' with query expansion);how do I display the words used for the query (i.e. 'album' and 'studio')?
Art
2010-01-18 21:18:51
If you want to know, which word does the result fit, then you can always add this word to the result, ie: select AlbumID, Title, 'albums' as SearchWordfrom Albums where match(AlbumDesc) against('albums') and do the same with other queries.
Michal Dymel
2010-01-19 08:36:20
A:
Use the following command
ALTER TABLE "table_name"
ADD INDEX " INDEX_NAME " (COLUMN_NAME)
Sreejith
2010-01-18 14:56:14
-1 I think this is only useful for a standard search not the full-text search the questioner desires (i.e. search for any word or words within the field)
Elemental
2010-01-18 15:24:02
+1
A:
Michal got the query syntax down, to create a full text index to assist this search use
Create FULLTEXT Index Test ON Table1(ColumnName1)
Much more at MySQL Docs
Elemental
2010-01-18 15:33:16