I want to do them in a multi insert transaction block for speed so that the indexes are only updated once after all inserts.

Eh, this is the first time I hear that MySQL only updates indices on a commit.

    for 1...100000 {
        $insert_sth->execute($_);
   }
[download]

Forget about indices and transactions. You're problem is doing 100000 queries. 100000 times the latency between your application and the database.

Want speed? Write a single statement^* and execute that.

^*If you would insert millions of integers, your query becomes larger than the max package size. Either increase that, or create a handful of statements, each slightly smaller than the max package size.