I have a simple query on a table with about 500,000 rows.
SELECT max(id) from <table_name>
In MySQL, this query takes 2 seconds.
In FileMaker ExecuteSQL- in the data viewer within FileMaker, it takes, depending on the attempt, anywhere between 45 seconds to a minute!
The "id" field is indexed in FileMaker so I don't know how to further optimize this SQL.
Other ExecuteSQL queries I've tried that use this 500,000-row table also seem to take forever (in FileMaker). I gave up on one sub-query after 5 minutes.
So, trying to do things more in a "FileMaker way", for acceptable performance, is there a way to do a "Find" where you can specify any of the FileMaker functions, like min, max, etc? I know you can't do RegEx, but how about the built-in FM functions? I don't want/need a "calculated field" since this is an ad-hoc query.
Not sure how to do this "Find" quickly. 45 seconds to a minute isn't going to cut it.
This might be a case where a plug-in could really help, but I'm probably missing something basic here about how to best use FileMaker to find the kinds of stuff I need.