Let's look at 3 cases:
** 300GB of data, 1GB of buffer_pool --> This means that a _random_ read will have to hit the disk 299/300 times. If you double the buffer_pool_size, then it becomes 298/300 -- That is _not_ a significant improvement.
** If you are always reaching into one spot (or a small number of spots) in the table, then even 1GB buffer_pool might suffice to make all accesses non-I/O. (This does not seem to be your case.)
** In between -- some randomness, but not totally random. Or some mixture of purely random and "hot spots". In these cases, a bigger buffer_pool will show a noticeable improvement.
> Also it's not possible to have multiple threads, since this way I will not add the records sorted by ID.
On the contrary... Each thread can do its own sorting. This gives you some benefit of sorting, and some benefit of multiple threads.
Let's pursue the PARTITION questions in one of your other threads:
http://forums.mysql.com/read.php?106,514281,514281
I still need to see the TABLE STATUS.
Going way back...
> More specifically the SELECT joins 2 tables and afterwards sorts the ouput by the primary key of the first table.
Why copy the record over? Can't you simply copy over the ids, then JOIN back to the original data?