12 Replies Latest reply on May 12, 2015 6:53 AM by TSGal

    Minimal Indexing Find Issues

    PaulSmith_2

      Summary

      Minimal Indexing Find Issues

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      MacOS 10.7.5

      Description of the issue

      A find on a Minimal index field containing only single-word data takes the same time as a "No Index" field.

      Possibly related: http://forums.filemaker.com/posts/ac07f9929e

      Test file available

      Also verified issue on FMP12

      It seems Find is not using the index, or the index is not populated properly.

      Steps to reproduce the problem

      1.) Create an field with minimal indexing
      2.) Create records with that field filled with *single word* unique values
      - I used GetContainerAttribute ( Get ( UUID ) , "MD5" ) and a script to do this
      3.) Create a script that times a find request on that field (of an existing value)
      - I tried both the double-equal "exact match" as well as other find operators
      - I used a variable of $start and $end for Get ( CurrentTimeUTCMilliseconds ) pre/post the "Perform Find" step.
      4.) Compare results to similar process on "No Index" field
      5.) Campare results to similar process on "Full Index" field
      6.) Compare results to similar process on "Minimal Index" with a word-separator appended to the field data.

      Expected result

      I expect the same result (for me, about 8ms) as other Indexed finds.

      Actual result

      I got the same result (for me, about 45ms & proportional to record count) as the "No Index" find.
      (All fields had "automatically create indexes" turned off.)

      Exact text of any error message(s) that appear

      Excerpt from Recovery Log (table called ShortTextField, an index is there):

      Timestamp,Message
      "2014-07-01 20:32:18.190 -0400","Recovery of schema skipped"
      "2014-07-01 20:32:18.190 -0400","Recovering indexes for table 'LongTextFields'"
      "2014-07-01 20:32:22.738 -0400","  Rebuilt value index for field 'Data Index Minimal'; 100000 item(s) now exist, difference from old index is 0"
      "2014-07-01 20:32:44.353 -0400","  Rebuilt value index for field 'Data Index Full'; 100000 item(s) now exist, difference from old index is 0"
      "2014-07-01 20:32:44.354 -0400","  Rebuilt word index for field 'Data Index Full'; 264893 item(s) now exist, difference from old index is 0"
      "2014-07-01 20:32:44.355 -0400","Recovering indexes for table 'NumberFields'"
      "2014-07-01 20:32:50.848 -0400","  Rebuilt value index for field 'Data Index Minimal'; 100000 item(s) now exist, difference from old index is 0"
      "2014-07-01 20:32:56.753 -0400","  Rebuilt value index for field 'Data Index Full'; 100000 item(s) now exist, difference from old index is 0"
      "2014-07-01 20:32:56.755 -0400","Recovering indexes for table 'ShortTextFields'"
      "2014-07-01 20:33:29.798 -0400","  Rebuilt value index for field 'Data Index Minimal'; 100000 item(s) now exist, difference from old index is 0"
      "2014-07-01 20:34:22.444 -0400","  Rebuilt value index for field 'Data Index Full'; 100000 item(s) now exist, difference from old index is 0"
      "2014-07-01 20:34:22.445 -0400","  Rebuilt word index for field 'Data Index Full'; 100000 item(s) now exist, difference from old index is 0"
      "2014-07-01 20:34:22.446 -0400","Recovering indexes for table 'Overview'"
      "2014-07-01 20:34:22.461 -0400","Recovery of custom function catalog skipped"

      Workaround

      Append a word-divider to the field.
      Use Full Indexing on the field.

        • 1. Re: Minimal Indexing Find Issues
          TSGal

               Paul Smith:

               Thank you for your post.

               Please send in the sample file so our Development and Testing can understand why this is happening.  Check your Inbox at the top of this page for instructions where to send the file.

               TSGal
               FileMaker, Inc.

          • 2. Re: Minimal Indexing Find Issues
            TSGal

                 Paul Smith:

                 I received your file.  Thank you.

                 I have forwarded your post along with the file to our Development and Testing departments   When I receive any feedback, I will let you know.

                 TSGal
                 FileMaker, Inc.

            • 3. Re: Minimal Indexing Find Issues
              PaulSmith_2

                   Just checking in to see if this issue is still being evaluated.

              • 4. Re: Minimal Indexing Find Issues
                TSGal

                     Paul Smith:

                     No action has been taken to date.  When I receive any update, I'll let you know.

                     TSGal
                     FileMaker, Inc.

                • 5. Re: Minimal Indexing Find Issues
                  PaulSmith_2

                       I'll assume this issue will not be resolved.  TSGal, thanks for taking care of your responsibilities, I just wish the Dev&Test Dept. was more responsive.  

                       They must be working on new dumbed-down features that we don't need while ignoring issues with current features... which seems to be an FMI trend.

                  • 6. Re: Minimal Indexing Find Issues
                    TSGal

                         Paul Smith:

                         With your sarcasm set aside, there is still discussion occurring on this issue.  The main issue is that All Indexing creates the word index and speeds up the find operations.  The Minimal Indexiing is for value operations such as field-based value list and Insert From Index features.  The discussion is more centered around documentation rather than performance.  I'll update again when I have more information.

                         TSGal
                         FileMaker, Inc.

                    • 7. Re: Minimal Indexing Find Issues
                      philmodjunk

                           Thus it is to be expected, but not clearly documented that Minimal indexing and No indexing produce similar performance for finds.

                      • 8. Re: Minimal Indexing Find Issues
                        PaulSmith_2

                             I apologize for the rant, I realize there must be a lot involved when working on mature software code.

                             So, the Find indexing functionality is consistent with FileMaker design, the documentation is the issue.

                             (If and) when the documentation is updated, try to include how each kind of index works... (finds, primary keys, secondary keys, value lists, sorting, etc.)

                             I believe this issue is closed now, there is already a documentation issue on file: Documentation Error regarding Minimal Indexes
                              

                             Thanks!
                              

                        • 9. Re: Minimal Indexing Find Issues
                          TSGal

                               Paul Smith:

                               From "Defining field indexing options" on page 185 of FileMaker Pro 13 Help - "Value indexes can be created for text, number, date, time, and timestamp fields, as well as calculation fields that return results of these same types. Value indexes are used for joining related records and for searches in number, date, time, and timestamp fields, and calculation fields that return results of these same types. A value index is created by taking each line of text (delimited by the carriage return character) and taking up to the first 100 primary character weights that all the characters in that line generate, according to the Unicode Collation Algorithm."

                               The entry can be found quickly by putting double quotes around the keyword, like:   "C95FE65550FF1A817521E1BBDA8DFDE6"

                               TSGal
                               FileMaker, Inc.

                          • 10. Re: Minimal Indexing Find Issues
                            PaulSmith_2

                                 Thanks TSGal,

                                 I have tried the double-quotes, the double-equal (exact match), etc. none seemed to trigger the use of the index for the find.  I am resigned to the idea that this is expected behavior, even thought it is not the desired behavior for me.  I'm afraid it would probably require some major changes in the Find functionality to function the way I want it to, which might degrade performance.

                                 I do consider this a Find issue regarding the way Finds use indexes, not an index issue affecting the Finds.  It would be interesting to know which index(es) Find uses and, in case one index is empty, if it falls back to the other or just ignores the other.  I don't think the public documentation gets that specific.

                            • 11. Re: Minimal Indexing Find Issues
                              TSGal

                                   Paul Smith:

                                   Thank you.  I have sent your comments to Development and to our Documentation manager for review.

                                   TSGal
                                   FileMaker, Inc.

                              • 12. Re: Minimal Indexing Find Issues
                                TSGal

                                Paul Smith:

                                This find issue with minimal index has been fixed in FileMaker Pro 14.0.1.  That is, finding on a minimal index field will be faster than no index.

                                TSGal
                                FileMaker, Inc.