Re: Stored Procedure too slow

[Rick James]

Your information confirms my guess...

> innodb_flush_log_at_trx_commit,1 -- says to flush the transaction to disk immediately after committing.
> autocommit ON -- says to treat each SQL statement as a separate transaction and commit immediately
> insert? 1 row at time -- says that you are inserting only one row per transaction.
> (unstated -- disk speed) -- an ordinary disk can handle only 100 operations per second.

Some speedups:

innodb_flush_log_at_trx_commit,1 -- change to 2 for more speed, at a slight cost of safety.

Whenever practical, "batch" your inserts -- that is, insert multiple rows in one statement.

When it makes sense to your application, do BEGIN ... COMMIT. (It will temporarily override autocommit.)