The ordinary FileMaker development model may be not effective for handling big SQL tables.
In this post, we will try to find out clues for handling big tables at a tolerable speed while avoiding the weird application behavior that is unique to SQL big tables.
Definition of termsBig tables: MySQL tables with more than 10 mil. rows
FileMaker/MySQL development: development using FileMaker for frontend, MySQL for backend
Filemaker-alone development: development using FileMaker for both frontend and backend
Problems of FileMaker's common interfaceFileMaker provides the common interface for both FileMaker-alone development and FileMaker/MySQL development, which make it possible that developers can use the almost same method in FileMaker/MySQL development as in FileMaker-alone development.
This is very nice, but as data of MySQL tables grow up, application users will notice a performance slowdown, the application's behavior will become so weird (FileMaker issues a lot of redundant and incomprehensible SQL queries), and operation may become almost impossible.
The following model is a tentative one for mitigating such problems:
Big Table handling model in FileMaker/MySQL EnvironmentThe figure below illustrates how to handle database objects.
- On MySQL, duplicate big tables and rename them as rpl_originalTableName which are used for replicating records SELECTed by users from original big tables.
- In FileMaker relationship graph, place only small tables and Rpl. Tables (do NOT place any big tables as it causes slowdown).
- Create stored procedures in MySQL to SELECT records in big tables and replicate them to Rpl. Tables, and INSERT/DELETE/UPDATE them whenever related records in Rpl.Table are inserted, deleted or updated. The stored procedures are executed from FileMaker's layout.
Common Model vs Big Table Handling ModelThe following table indicates the comparison of the common model which is commonly used in FileMaker application development and the Big Table Handling Model(BTHM) based on the figure above.
|Ease of operation||Good||No so good|
|Performance for big tables||Very poor||Good|
|Records order||Weird||OK||Maybe detailed later|
BTHM may not be appropriate for the found set of over 0.1 million, and may not be appropriate for the large number of simultaneous users.
The above two models plus 1 are illustrated in the following video.