How do you add a column to a fulltext search in MySQL to indicate which words were included in the search?
3 Answers
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)
2 Comments
Art
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 Albums where match(AlbumDesc) against('albums') union select AlbumID, Title from Albums where match(AlbumDesc) against('albums' with query expansion) union select AlbumID, Title from Albums where match(AlbumDesc) against('studio') union select AlbumID, Title from Albums where match(AlbumDesc) against('studio' with query expansion); how do I display the words used for the query (i.e. 'album' and 'studio')?
Michal Dymel
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 SearchWord from Albums where match(AlbumDesc) against('albums') and do the same with other queries.
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
Comments
Use the following command
ALTER TABLE "table_name"
ADD INDEX " INDEX_NAME " (COLUMN_NAME)
answered Jan 18, 2010 at 14:56
Sreejith Sasidharan
1,3687 gold badges27 silver badges48 bronze badges
1 Comment
Elemental
-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)