Find auto-optimization with multiple criterias

Idea created by Vincent_L on May 8, 2016
    • brsamuel
    • mark_scott
    • Vincent_L
    • SteveNoble



    Experienced devs know that the order of the search criterias is vital to get decent performance when doing a multi-criterial find. First do a find on fully indexed fields, then constraint the foundset by the unstored ones. And if you know it, use the most discriminative indexed criteria first.


    So, with scripted finds, experienced devs will always break a multi-criterial find in one find, plus many constraint finds.

    Unfortunately newbies don't know about this and get burned by slower than possible finds, they think that FMP is slow.

    Moreover, when using quickfind there's no way* to do that kind of optimization.


    Also, for experienced devs breaking down the search in several steps is a bit time consuming, and adds extra steps.


    All of this would be fixed by a simple optimization : Filemaker would break down every multi-criterial search itself, just ordering the fields with indexed first and then unstored ones. That would make newbies and quickfind searches must faster.


    But it could go a bit faster than that, even for experienced users. It will first search the number indexed fields as numbers are faster to search than text, and then the text (or maybe the date / timestamps ones if they're faster than text I don't know). Using FMI engineers knowledge it would first do the finds on the faster field types (experienced dev could also do it, only in scripted finds, but then that's an extra layer of complexity to think of).


    Those are two very easy ways for FMI to implement. And it would save a ton of time and make filemaker more convenient, it would augment quickfind usefulness by reducing the probability of slow searches.


    But that's not all, Filemaker has other tricks in its sleeves and could go further than experienced devs. That's because filemaker knows the index complexity of all indexed fields. So it would first search the indexed fields where the index is the biggest = that's the most precise / discriminative fields.


    Also unstored fields can be certainly ordered (foreign table indexed fields, then unstored field of the current table, then foreign unstored fields)


    So the perfect optimized order would be


    - Order by index field size descendant


    - Order by field type quickness ascendant

    - unstored field : which could be broke down by just foreign table indexed fields, then unstored field of the current table, then foreign unstored fields.


    That final approach with index item count and maybe even kind of unstored fields, is best handled by filemaker, because as the data set changes, or even the database structure changes, Filemaker would always use the best possible optimization. Whereas the experienced devs scripted search could be less optimized as the database changes.


    And of course the same could be done for relationships.


    This would be very beneficial for newbies, make them feel the filemaker is faster, and would save considerable time for experienced devs (and possibly give them even faster searches)


    *Experinced devs could intercept the user search and do the breakdown with a script, but that would need to be set on all layers, and it's complex, way over the head of newbies.