Storing MySQL Index in RAM
By default MySQL tries to store its index information in RAM to speed up database access. You should use your performance monitor to see how much spare RAM you have available and then tweak the settings to get as much of your index into RAM as you can.
To see how large your index files are, look in your DATA directory, in the subdirectory named after your database. Look through the .MYI files to see how much space they take up. Those are your index files.
Now check out your MY.INI file, in your main MySQL directory. You might want to check your services entry for MySQL to make sure this is really the ini file your system is using :) In that file you want to look for -
This is the amount of RAM put aside for holding index information.
Checking on how well you're doing is pretty simple. From a MySQL prompt, type in -
show status like 'key%';
It'll give you some stats. Compare the key_read_requests with the key_reads. So say for example you have 10,000 key_read_requests ... and only 10 key_reads. That means that pretty much all requests were satisfied by memory, that only 10 of them had to actually go to the database to get a value. You want that ratio to be very low - that out of all the requests, only a few went to the database.
ASP Server Setup Information