Thanks for posting!
If database optimization would need to be done for an ODBC import, I would imagine it would be on the MySQL side as that's where the query is processed. Let's do some tests to figure out where the bottleneck lies.
Do you get these same results when connecting to the data source from a different program, like Excel?
Is this reproducible in a brand new database?
Are you able to reproduce this when connecting from the same database on a different machine?
Do you have any other data sources that you can test with?
Thanks for the reply.
We are able to run similar queries through php (by passing odbc) and there is no freezup. We have not tried running queries connecting through the same data source with another program, I will try that.
We run a single filemaker server connecting to a mysql server through odbc so bringing up a brand new database server might be tough, and the freeze up happens across all clients using the filemaker server.
During the lockups there is no unusual activity in sql server other than connections piling up, the cpu and memory usage are normal, and there is very little disk activity. We either wait it out, or we have to kill processes coming from the odbc filemaker user connecting to the mysql database.
I guess you are using FileMaker's ESS feature to access MySQL tables dynamically.
In the ODBC Administrator, did you enable Connection Pooling for the driver?
If not, try enabling it and see if the situation changes.
Pool Timeout: 120 sec / Retry Wait Time: 60 sec would be good for a start.