How I got a list with data from MySQL in less than 1 second with a SQL VIEW - from an awful 20-25 seconds when going through relationships between multiple tables from the MySQL database.

My Filemaker solution is integrated with a MySQL database that has been highly normalized for different reasons.

This means that a simple list with 4 different fields can easily contain info from 4 different tables, where a couple of the relationships might span 3 tables. This made the performance in FM really awful. In my case, this easily meant 20-25 seconds to pull out a list of 50-60 records from a total of 3000 records in the parental table.

Not really acceptable, and I had to find a solution to it.

These were the solutions I considered:

1.

Update a shadow table in FileMaker each time the list was to be made, and perform the find on the shadow table. The shadow table would mainly contain the info the list needed, pulled from all the different MySQL tables.

But I wasn't sure about the performance, and I was afraid of not managing to get it implemented in all the scripts different places.

2.

Make a Execute SQL query, parse the data and add it to the correct fields with loops, but this meant a lot of scripting in different places, and it got really complicated getting the right info into the right fields each time.

3.

I also considered making some new relationships in FM, in the Anchor-Buoy style, but cluttering up my relationship graphs with even more things (it already contains more than 60 unique tables + some "duplicate" TOs), and probably only gain a little bit performance wise, really didn't seem too tempting.

4.

Then I read about MySQL views. This can be looked upon as a saved SQL query, making it easy to get info from different tables into one view, filtered and sorted. I suppose it can also be likened a bit to a FM layout with a search and sort performed at entering the layout.

In the MySQL community, these views seem to be frown upon, and performance can apparently be really awful if a view is based on an other view.

But for Filemaker, MySQL views mean that the processing is already being taken care of in the MySQL server, so that FM does not have to get all the info from all the external tables, process through a bunch of relationships and then spit out the wanted result.

(Without really knowing how FM communicates with SQL databases, that means that views might also be a good idea to use if you only use a few fields in a table, especially if the table also contains big comment fields or the like. But I haven't really checked it out).

In my case, where all I needed was 4 fields from 4 different tables, but actually spanning a total of 6 tables (two intermediary tables as well), making a SQL VIEW seemed promising.

First, I needed to go into Manage External Data Sources, Edit Data Source, and check Filter by types "Views" by the bottom.

This means that views will be listed in the same way as a table from the SQL database.

In FIlemaker, I have made myself a sSQL table, where each new record has a field for a SQL statement with a comment field, a name field and a Excecute OK timestamp field attached. Plus buttons for the Execute SQl script and duplicate record on the layout.

(And a second SQL statement field, result field and Excecute button for SELECTing the result, or SELECT count(t.uniqueField) FROM viewName, to easily see the result).

This way, I could fiddle around with different VIEW statements until I got it right.

The main statement is

CREATE VIEW viewName AS

SELECT

...

(And ALTER VIEW to make changes to the same view.)

The main thing to know, is that a view does not ahve any indexes itself. In native Filemaker tables, each record has a hidden unique key. In external ones, Filemaker thus needs you to specify a field, or a combination of fields, that will contain unique values for each record.

In my case, the following statement worked:

SELECT

....

FROM tableWithUniqueRecords t

LEFT JOIN ... AND language_ID=y

While a where statement did not do the trick, returning a different number of records:

LEFT JOIN ...

WHERE language_ID=y

There are many different JOINs, so make sure you get the right one for your solution.

In my database, there were some orphans here and there, both form my early stages of making a FM interface, and from an other program we had bought, that had contained some faulty SQL statements.

I thus had to spend quite a lot of time washing my data, but that was probably needed anyways.

For one of my views, I only needed to get one result from the child table. Just like when it might sometimes be OK to put a field from a child table on a parent layout instead of inside a portal, only showing one of the children.

For the views where this is necessary, all you have to do is to add the folowing statement to the bottom of the SQL statement:

GROUP BY t.unique_id

Then you ensure that the view will only have unique parent records.

I am sure many real developers already know about this, and hopefully have some more information on it.

But I didn't really find anything, and it just saved me so incredibly much development time.

Plus whooped my performance:

Finding a list with 50-60 records (from a total of about 3000 parental records) in the old way, took a whooping 20-25 seconds.

Changing the layout source and the fields on the layout to reflect the new ViewName table occurrence took me about 1 minute to change. Finding the list with 50-60 records now takes less than 1 second - or only 1/20 the time it used to!!!

So the performance in my solution really skyrocketed with the addition of a few views to the SQL database.

I would be really interested in getting some performance info on different types of views from real developers with big test databases and testing kits, but my main reason for writing this post, is to hopefully help someone else struggling with the same problems that I did.