AnsweredAssumed Answered

Why is ExecuteSQL So Slow (what's the alternative)?

Question asked by disabled_morkus on Feb 19, 2016
Latest reply on Feb 21, 2016 by disabled_morkus

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.




- m