How many millions can FileMaker handle?
Does anyone know what the upper limit for records would be with FileMaker? Like what about 10 million records? Can FileMaker handle that? I mean in the sense, they searches are reasonably quick.
Also, what would be the techniques for improving the speed of a search with 10 million records? I have several books on FileMaker, and not one of them addresses this issue.
For example, I would assume that an integer search is quicker than a character or string search. So that would mean putting address numbers in a numeric a field separate from the street name. I know by reading on MySQL that integer searches are quicker than string searches (at least for MySQL).
Then what about enumeration? Like when you allow only one value from a value list. This helps search speed with MySQL, but does it help search speed with FileMaker (I'm assuming using a value list in a field's Validation Options ("Member of value list: ") might be the same or similar as an enumerated type)
Or what about converting text to numbers, in a field set aside for such. For instance, if A=01, B=02, et (via a case function) then concatenate the numbers (as text) and then store the result as a number (in yet another field, *number). Then to convert a search (via calculation) from a string to a number and searching for the number within the *number field?
Or does having multiple indexes help? For example if one is searching for Don Juan @ 1234 Elm Street, Chicago, IL if the search were to use "Do" in the nameFirst column, "Ju" in the nameLast column, "1234" in the number of address as (number type) column, "Ch" in the city field, "IL" in the state field, would that be faster than searching fewer fields? (and the text to number calculation could also be applied here, within a script).
Or does it help to intentionally run a find twice instead of just once? Like once for the State, and then to run a Modify Find to search within the Found Set?
Or what about specifying a limit of characters in the validation tab of a field's options? Does that enhance speed of finds?
I suppose compacting a database (to a copy that is used in replace of the original (to be safe about it!)) will also help speed of finds.
Oh yeah, what about the cache too? The default is 8MB. How do you determine how large to set the cache?
I mean what are the tricks (beyond have a fast cpu, fast disk, lots of ram, plenty of free disk space? And how big record-wise can FileMaker reasonably get? Lets assume 50 or fewer columns (which is probably more than I'll ever use, `I think`).