The differences between 5400 RPM, 7200 RPM, 15000 RPM are 'substantial' in comparison to a mechanical drive, but 'minimal' in comparison to most SSD.
The 'gain' is the lowering of the lag-times in map, etc loading. If your game requires the use of the CD/DVD then create an image of it to your HDD wherever possible; drive emulators.