AnsweredAssumed Answered

Indexing and performance - key field: integer or text

Question asked by jmmx on Apr 10, 2014
Latest reply on Apr 23, 2014 by TSGal


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?