MySQL Optimisation Strategies to Improve ESS performance

Filemaker’s ability to connect to any of the major SQL databases without having to do any SQL is a real killer feature but performance can often not be what it might. Often this is caused by Filemaker having to hammer the SQL data source. There are a few tricks that can move a lot of the work onto the SQL side.

I am going to describe a few techniques and demonstrate examples on MySQL. Oracle and MSSQL have the same (and actually substantially better implementations in some cases) features and because SQL is generally pretty safe across the three these examples might even work on all three platforms.

Temporary Tables

Temporary tables exist as table only visible to the user that created them. When the connection between the user and server is dropped so are all the user’s temporary tables. In all other regards they function as a normal table.

Have a few useful features, mainly in that they intercept the requests if they share a name with a table. For example if we create a temporary table as:

CREATE TEMPORARY TABLE IF NOT EXISTS t LIKE t AS SELECT * FROM t

This will give us a temporary table, exactly the same as the original table t and from a users point of view they will never know they have been changed.

The power of this approach comes from manipulating the

 SELECT * FROM t

code to get the MySQL to do some heavy lifting. For example we could narrow a table to just my records with:

SELECT * FROM t WHERE t.user = 'damian'

The biggest disadvantage of this approach is changes written to the temporary table are not written back into the original table. You can work around this using a trigger on insert or maybe consider an updatable view. Both approaches are detailed below.

Views

The SQL purists maybe get a little animated but a view has a lot in common with a temporary table. They are specific to the user/mysql server connection, they are temporarily constructed (ie dropped at disconnection) and can be used to leverage the server to pre select records. The main differences for our purposes are that views are automatically created and can be used to update the underlying data. Views share the name space of tables, which is a complicated way of saying views can not have the same name as a table.

Now the complicated bit. As I said earlier views can update the base table but there are a few caveats. First the correct algorithm has to be selected in the create view statement. The nice thing is if you leave out the algorithm MySQL does its best to allow updating. Second the query has to produce a table with a one to one relationship back to underlying data. This effectively rules out summary functions and unions.

So why do we use views? Well the biggest saving here is getting a sub set of records into a table before Filemaker gets its grubby paws on the data. As you might have noticed Filemaker can be a little slow so moving the queries to the MySQL server is obviously a good thing. Second we can use the view as a method to apply security and safety to an existing MySQL database.

Triggers

Triggers allow us to run SQL on the server based on events. The main difference between Filemaker and SQL triggers are SQL triggers are fired on events based on the database rather than the UI. Within MySQL there are three events (insert, update and delete) which can fire a trigger, the triggers can be fired before or after the event giving us six possibilities.

Triggers execute an SQL statement allowing you to perform updates or data validation but they can not prevent the event from ocurring.

Damian Kelly

Damian has been a database developer with Linear Blue since 2007. During this time he has developed complex FileMaker solutions in a broad range of industries from Finance and Investment to Manufacturing to New Media Promotion. Damian graduated in Production Engineering from the University of Hertfordshire in 1996, attained his Masters of Engineering from Kingston University in 1997 and MBA from Heriot Watt in 2006. He holds certification in FileMaker versions 7 through 12 and in Oracle MySQL 5.

More Posts - Website

4 thoughts on “MySQL Optimisation Strategies to Improve ESS performance”

  1. Hey Damian, great post indeed! Would be cool to have some sample file demonstrating this, but you’ve definitely pointed me to a good direction. Thanks for sharing.

  2. Informative in the quest for filemaker mysql performance. Do you have any example scenarios using each method?

  3. Thanks for this post. I want to try out this technique on a mysql dataset of 12 million records. Now that FM14 has been released, and since your post is 4 years old, has anything changed? Any easier ways or alternative techniques?

Leave a Reply