Re: Slow INSERTs on BIG tables.

[Ingo Strüwing]

Performance tuning is not one of my best skills. I would expect that the people in our "Performance" forum are much better. But I will share some of my thoughts anyway:

ENABLE KEYS rebuilds all indexes from scratch. So this is only advisable if you update a majority of rows in a go. Due to my lack of experience I cannot tell what a "majority" is. I suspect somewhere 50% and 90%. Anyway, if I understand your application correctly, you are far below this "majority". So forget about disable/enable keys.

IMHO, the best you can do is to look if you can get rid of one or more indexes. You should weigh the negative performance impact of indexes for updates against the positive impact on selects. If you have the chance (a second table for testing over night or so), drop all indexes and see how the modifications go. Then you would know what you could win by dropping indexes.

What you did not tell us is if the machine is I/O bound or CPU bound. During selects and during updates. Assuming I/O is the problem, you may try the following:

Another thing you could try is to increase 'key_cache_block_size'. Perhaps to 4096. This would give you bigger I/O chunks, but in most cases also overhead.

Add more RAM to the machine and increase 'key_buffer_size'.

Ingo