If I run the database on a server that has enough ram to load all the indexes and tables into ram. And then it would update the index on the HDD every x seconds. Would that work to increase performance dramatically?
Perhaps. Probably not dramatically though. If x seconds (called a checkpoint) is not long enough for the entire index to have been dirtied, then my finding is that writing half of the pages (randomly interspersed) of a file, even in block order, still has the horrid performance of a long sequence of random writes, not the much better performance of a handful of sequential writes. Although this probably depends strongly on your RAID controller and OS version and such, so you should try it for yourself on your own hardware.
