2 Replies Latest reply on Apr 23, 2014 11:31 AM by TSGal

    Indexing and performance - key field: integer or text



      Indexing and performance - key field: integer or text


           As we all know - every table needs a key field (assuming not a set of key fields - let's keep it simple).

           I have seen an application in which the originator used text based keys that were auto incremented ("0003", "0004"...). They look just like integers but are text. As far as being a key, as long as each is unique this is fine, but I was wondering about other implications.

           Integers (to my mind) are the simplest data items in computer memory, and the smallest. Therefore it would seem to me that using strict numbers, instead of text numbers,  would improve performance, especially for indexing and sorting. In theory this should be the case, my question is:

              How much difference will this make in a the real world of FMP where tables might hold some 10s of thousands of records?

           Related: Question - internally how would a 5 digit text number be stored? That is, how are the ASCII characters stored into words?


        • 1. Re: Indexing and performance - key field: integer or text

                    As we all know..

               Well I disagree. Some tables work just fine without such a field. But they a usually small, special use tables so I am just quibbling over details..

               I suspect that you are right, but have never run any tests nor have I seen any test reports to confirm it. I doubt that it makes a large difference except maybe for truly large numbers of records, but then I prefer to use auto-entered serial numbers with fields of type number in the first place as a combination of the KISS method plus the fact that I may need to sort on that field at some point and it won't sort correctly if it's a text field.

               As to how data is stored internally, you'd have to ask a FileMaker engineer and they won't be allowed to tell you (I think) due to the info being considered proprietary.

          • 2. Re: Indexing and performance - key field: integer or text


                 Thank you for your post.

                 If fields are indexed, numeric values will be generally shorter than text values.  That is, the indexing space will take up more room with text values than with numeric values.  The performance will be minimal since each value is associated with an internal record number to locate the record.

                 A "five-digit text number" (like a zip code) will be stored as five characters.

                 FileMaker, Inc.