SOSL Search Results incomplete?

Question

I have the following SOSL:

FIND {*ball*} IN ALL FIELDS RETURNING Campaign(Name, Subscription_Center_Description__c)

I have the following 3 campaigns that SHOULD show up:

1. Name: Sportsball, Description: The sport that utilizes a ball and the players will do things with the ball to score many of the points. The one with the correct number of points at the end of the sportsball game will be victory.
2. Name: Baseketball, Description: probably the silliest sport ever invented...
3. Name: Football, Description: The one you play with your arm feet.

Note:

I also have Name: Futbol Description: The one you play with your foots.

But that's not relevant to the question.

The SOSL query is only displaying Sportsball as an acceptable result for the query and I am having a heck of a time trying to figure out why baseketball and football are not showing up.

Answer 1

It's working as expected. Wildcards at the beginning of the word are not directly supported. From FIND {SearchQuery}, there's a specific note:

Asterisks match zero or more characters at the middle or end of your search term. For example, a search for john* finds items that start with john, such as, john, johnson, or johnny. A search for mi* meyers finds items with mike meyers or michael meyers.

If you are searching for a literal asterisk in a word or phrase, then escape the asterisk (precede it with the \ character).

(Emphasis mine.)

The only reason why Sportsball appears in the search is because the word ball is in the description:

The sport that utilizes a ball and the players will do things with the ball to score many of the points. The one with the correct number of points at the end of the sportsball game will be victory.

(Emphasis mine.)

If you'd written the name as "foot ball", for example, then it would have been found by name.

The index starts at the first character of each word, and so it cannot search starting at any other character position within the word. The indexer also ignores common words, like "the", since it would bring back many useless records.

Also, you might want to check out How Does Search Work? for more information about searching in general.