I am dead set on moving over to a MySQL database for some huge data sets I'm working with but right now I don't have the time. In the meantime I am curious about a technical performance issue regarding speed between the two methods.
Obviously binary jumping on a huge sorted file is a nasty hit to performance. The chances of finding the data you need in the chip cache or even memory is pretty bad if you assume a statistically normal distribution of record requests across the huge file. Unless the whole file is in memory, and some of the ones I am working with are 20GB so this is impossible on the Win32 system I am using, it's almost a certainty that a large number of record requests will degrade to the performance killing operation of an actual hard disk read.
Since I have never done any database index programming other than some really simple B-tree indexing, I wonder how good the indexes created by modern databases like MySQL are at avoiding hard disk hits. Obviously one big advantage they have is if the index keys are a lot smaller than the data records they represent, you can jam a lot more of the index pages, if not all of it, into memory and that avoids a lot of disk hits. But I was wondering if the code behind those indexes can successfully optimize in other ways, especially when it comes to index page access prediction, to speed things up even more by making sure most index page accesses don't result in disk accesses?
If anyone has had any deep experience with the kind of code underlying MySQL indexing or similar entities, or has done some deep performance testing, I'd like to hear about it.