Mastering Field Indexing within Claris/FileMaker Pro 

Yes, you are correct. You can always use search operators and you can also search unindexed fields as well. I was probably thinking about something else and made the mistake of saying you can't search for part of the email. Most of my personal search implementations utilize a "single field search" a la Google style. This implies that neither the end user nor the developer will be using search operators. Thus, if you do switch to unicode, and you're not going to rely on QuickFind, you will still find yourself (in some cases) needing to implement an automated way for a query to integrate search operators if you're going to present a user with a single field for searching. For example, using an OnObjectExit or OnObjectSave and, using a script in order to introduce search operators which many users are unfamiliar with. Again, thanks for pointing this out and it wasn't my intention to mislead people into thinking it can't be done. So, to all you newer developers out there... You can ALWAYS search on any field (indexed or unindexed - indexed will be faster of course) and you can always use the native search operators. You can also search using SQL (translated from SQL into native FileMaker search) and then there's Quick Find. Thanks for pointing it out. I also had another long-time subscriber Ralph L point this out. Thanks again!

Further clarification. The likely reason I made the mistake about saying I could not find 'editor' within editor@filemakermagazine.com was actually based on the inverse situation. If you try to search for 'editor@filemakermagazine.com' (the full string) directly on a field set to a language other than Unicode AND the index is either Minimal or All, you won't get any results because the @ symbol is interpreted as a search operator. I needed users to be able to simply paste in the full email address and have the @ symbol seen as an @ symbol - not something that translates to 'any one character'. Hence going to Unicode sees the @ as part of the string but then case sensitivity comes into play. The test is this: Create a field called email. Put any email value within it and then try to search for the full email substring. The ONLY way you'll get a result is if you search for 'email\@example.com' because you have to escape the @ symbol because it is used as a search operator and won't find itself. Thus, the only solutions for being able to find a full email string is to A) use Unicode and either UPPER() or LOWER() on both sides, data storage and query construction or B) integrate automated search operators or escaping or C) teach users how to use the search operators or D) create your own custom automated search which will adjust/account for word break characters and escape them or inject search operators. Interestingly, it looks like Claris may have made a fix or change to Quick Find. I was able to get results when searching for a full email string with the @ included. I seem to remember at some time this not working - but it could just be me getting along in years. :)

Hi Matt, after talking to other Filemaker developers about this yesterday and someone reporting a performance issue with Minimal Index, I did a little test today: I have a "small" DB with only a few fields, but with 1.6 million records. It has one field which contains a UUID. All speed tests are done with this ID field and I always searched only at the beginning of the field. Here are the results: Indexing ID field = None / Unicode File size = 290MB Searching for the whole or a part of the UUID took on average 8.7 seconds. Indexing ID field = None / English File size = 290MB Searching for the whole or a part of the UUID took on average 12 seconds. Indexing ID field = Minimal / Unicode File size = 502MB Searching for all or part of the UUID took 9 seconds on average. Indexing ID field = Minimal / English File size = 502MB Searching for the whole or a part of the UUID took 11.5 seconds on average. Indexing ID field = All / no matter if Unicode or English File size = 715MB Searching for the whole or a part of the UUID took 0 seconds! (I could not measure time manually => it was instantly!) For me it is not obvious what Minimal Indexing does exactly!? Especially because the search in the not indexed ID field was nearly identically fast than with a Minimal Index...!

It always pays to know exactly how something is working and how it might impact your decisions related to it. I'm glad it's proving helpful! Thanks for the comment.

The test would need to be something where you move a lot of fields which may be set to an All (word index) to Minimal (value index) when only the value index is required. Given that the biggest impact is likely the file size, the biggest issue with regards to performance is the fact that Quick Find is on every field by default. This type of search, in most cases, is the likely culprit of any performance related issues. Even if you have all fields set to a full word index setting, the fact that the data is likely sitting on a fast SSD disk and is retrieved server side means that performance is likely not that much different. If you were on a spinning disk this would be a different case. But, even still, searching more fields than necessary, across more data than necessary (e.g. accidental word indices vs. suitable value indices) is always an area where things can be optimized.

Thank you for the feedback! I'm glad it's proving helpful to some for understanding how field level indexing works in FileMaker.

Awesome!