I'm trying to optimize an important cronjob for my program, and what it basically does is generate a long list of user ids with their score points. I have a table called user_ranks and it will bulk insert all of that data into that table. Normally that went fine, but it is taking a longer and longer time, which now is giving huge problems to my website. The bulk insert query takes about 5 seconds, which is too much. It is blocking other queries to that table. (I'm using row level locking but it locks the whole table)
Now what my idea to optimize this is, is to do the bulk inserting in another table, say user_ranks_new and then after the insert is done, rename the
user_ranks table to
user_ranks and afterwards dropping the old table.
This is obviously a lot faster than blocking the important table (
user_ranks) for about 5 seconds.
This cronjob runs every 10 minutes, so I want it optimized a lot.
Are there any drawbacks to creating lots of tables, renaming them, and dropping them? Does the ibdata files gets a lot bigger, or any other?
p.s. I'm aware of settings like shown on this page (http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html), but they didn't really help significantly.