First, do yourself a favor and take the MEMORY engine suggestions with a large grain of salt.
Unless, of course, you don't need any of the following:
MVCC
Transactions
Multi-threaded access
Clustered indices
Foreign key support
More than an occasional INSERT/UPDATE
Your data
If you don't need any of those, then you shouldn't be using MySQL. Use a simple key-value store like memcached.
If you need any of those (hint: you do), then use InnoDB, as much RAM as you can afford, and a properly configured innodb_buffer_pool_size. The MySQL Performance Blog has an excellent article on configuring innodb_buffer_pool_size. Short and sweet - you'll want enough RAM to fit the entire DB tablespace and indices into RAM, and set innodb_buffer_pool_size to 70-80% of total system RAM for a dedicated MySQL server.
Another option would be to create a ramdisk and use MyISAM or INNODB tables within that. I would imagine, though, that performance would be better using the MEMORY Storage Engine, due to the fact that it was designed from the ground up to be stored in memory, whereas the other table types operate under the assumption that they're being stored on disk.
RAM SAN very very expensive but mind blowing IO. RAMdisk is a very very bad idea because in the event of a loss of system power or a fatal system error the RAMdisk is gone as it is in RAM.
Try native mysql query cache function wich comes natively disabled and makes huge difference on static/semi-static tables queries.
Try to NOT sort huge queries at mysql, throw the raw data into code and sort it there then cache.
Try to raise mysql memory limit.
You might want to put some monitor (like munin) on your server to make sure that mysql is the real problem, usually it's very disk expencive when you're dealing with non cached queries. If everything fails you will need to expend some coin getting a SSD storage solution.
Play with the MEMORY storage engine as ErikA suggested. But make sure you are also optimizing all of your query cache, thread cache and buffer settings. mysqltuner.pl is a great starting point.
First, do yourself a favor and take the MEMORY engine suggestions with a large grain of salt.
Unless, of course, you don't need any of the following:
If you don't need any of those, then you shouldn't be using MySQL. Use a simple key-value store like memcached.
If you need any of those (hint: you do), then use InnoDB, as much RAM as you can afford, and a properly configured
innodb_buffer_pool_size. The MySQL Performance Blog has an excellent article on configuring innodb_buffer_pool_size. Short and sweet - you'll want enough RAM to fit the entire DB tablespace and indices into RAM, and setinnodb_buffer_pool_sizeto 70-80% of total system RAM for a dedicated MySQL server.How about the MEMORY Storage Engine?
Another option would be to create a ramdisk and use MyISAM or INNODB tables within that. I would imagine, though, that performance would be better using the MEMORY Storage Engine, due to the fact that it was designed from the ground up to be stored in memory, whereas the other table types operate under the assumption that they're being stored on disk.
RAM SAN very very expensive but mind blowing IO. RAMdisk is a very very bad idea because in the event of a loss of system power or a fatal system error the RAMdisk is gone as it is in RAM.
You might want to put some monitor (like munin) on your server to make sure that mysql is the real problem, usually it's very disk expencive when you're dealing with non cached queries. If everything fails you will need to expend some coin getting a SSD storage solution.
Play with the MEMORY storage engine as ErikA suggested. But make sure you are also optimizing all of your query cache, thread cache and buffer settings. mysqltuner.pl is a great starting point.