Fulltext searching

[ginerjm]

Looking to improve on a fulltext-keyed table search process.  I have a MyIsam table with  a primary index as well as a large varchar column keyed as fulltext. What I would like to learn more about is how to do a search on the fulltext key that uses multiple words.  The text field is a set of meeting minutes so there is quite a bit of text in ordinary English.  I'd like to improve my current searching capability to help me to do this kind of expanded searching.  Right now I can only manage to search on a single word which limits my searching capability and results.

I have recently started reading up on this and have gotten myself in pretty deep with Natural Language, Distance and using "" on the search words but it doesn't seem to do what I want to do.

Ex.  Say I want to find a sentence/phrase that uses "M/C", "donation" and a dollar value indicated by the presence of a $ sign.  I apparently am not allowed to do that cause I get no results.  

And that is why I am here.

Here is the query I currently have tried:

SELECT meet_date, meet_txt FROM meeting_mins WHERE Match(meet_txt) against('"M/C donation $*" @10 ' in Boolean mode)

The above is looking for the existence of 'M/C' and 'donation' and a $ with anything after it.   All within 10 words of each other.  At least that is what I think I'm composing.  I have also tried it without the $ item and again with no slash in the M/C item.  No results for any of these.

Any mysql aces know what I need to do to accomplish the query I included?  To be clear the column 'meet_txt' does have the fulltext index defined. It is using the MyIsam engine and the utf8_general_ci character set.

To add some more complexity - would it be possible to derive a position value for where in the field the search was successful, or at least the first place it was?

[requinix]

FULLTEXT searching just does not do that. It doesn't offer particularly sophisticated features: it's oriented towards taking an input list of words and trying to find things that are relevant to those list of words.
"M/C" isn't going to work because those aren't words. Prices aren't going to work because those aren't words. "Within 10 words of each other" isn't an option.

[gizmola]

To add onto what requinix noted, I believe the default for fulltext is words greater than 3 characters in length, so by default it would already have an issue with 'M/C' even if that was interpreted as a word.  You can tweak the default length, but I haven't done anything with mysql fulltext search in a while, so I can't tell you off the top of my head, what you would need to do there.  Otherwise, you can think of full text as breaking up input into individual words and letting you search for rows that include some or all of those words, with the results "scored" such that it will give you back results that have the highest score first.  I think the assumption there is that the user will need to look through the results and determine if what they searched for meets the criteria.

I want to add that there are many fulltext search systems out there that you can integrate with your app, which have additional capabilities beyond what mysql offers, and there are often php component libraries available that make working with these other solutions easy.  This forum is a good example of that, in that the search features use sphinx, but there are many other options available.  Sphinx is popular due to its performance (written in c++) and relatively small footprint, but there are a lot of other options out there.  To name just a few worth looking at:

• Solr
• Lucene
• Elastic Search
• Manticore
• Typesense