How to speed up MySQL INSERT / UPDATE?

Question

I have three large MySQL tables. They are approaching 2 million records. Two of the tables are InnoDB and currently comprise about 500 MB. Another table is MyISAM and is about 2.5 GB.

We run a script import from FileMaker to insert and update records in these tables, but recently it has become very slow - only pasting a few hundred records per hour.

What can I do to improve performance to make inserts and updates faster?

Answer 1

It turns out that the reason for the slowness was aloof from FileMaker. Exporting FileMaker entries to CSV and executing INSERT / UPDATE commands resulted in very fast execution.

Answer 2

For INSERT, this may be related to the indexes you defined in the tables (they must be updated after each INSERT). Could you post additional information about them? And are there any triggers installed on the tables?

For UPDATE, this is a completely different story, it is possible that the update is not slow, but the record search is slow. Could you try changing UPDATE to SELECT and see still slow? If so, then you should examine your indexes.

Answer 3

For an Innodb table, if this is an acceptable risk, I would consider changing the level of innodb_flush_log_at_trx_commit . A few more details on this blog post , along with a few Innodb tuning pointers.

For both engines, INSERT batch processing together can speed things up to a point. See doc .

What version of MySQL are you using? There have been many improvements with the new InnoDB "Plugin" engine and concurrency operations on servers with multiple processors.

Answer 4

Is the query slow when executed in MySQL from the command line?

If you use FileMaker's Execute SQL Script step, which connects and disconnects after each call, causing a significant slowdown when making a large number of queries. We had clients who switched to our JDBC plugin (refusal of self-promotion here) to avoid this, which led to great acceleration.