Below are some quick tips for MySQL tuning that I have learnt from my colleagues and books. To keep this article short and useful as quick reference, I will just put the common ones here.

• key_buffer size. (for MyISAM - default at only 8MB)
• innodb_buffer_pool_size (for InnoDB - default at only 8MB) - reference

You need buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces) because it does not only contain data pages - it also contain adaptive hash indexes, insert buffer, locks which also take some time - Peter Zaitsev

• Bumping these values up can decrease disk IO and make your database run a lot smoother.Use indexes. Enable the slow query log and look for queries that show up most often, then learn the explain command on them.
• Check Created_tmp_disk_tables in show status; This value usually goes in hand with huge queries that aren’t using indexes. If it keeps increasing it means you are having to create on disk files to execute the query. Consider increasing tmp_table_size or putting your disk files on a TMPFS by setting tmpdir = /tmp.
• Use the query cache if it can help you. Check the query_cache_size. Check how well it is performing. You can do this by doing a ’show status’ and then using this formula:

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

• If there are a lot of Qcache_lowmem_prunes, that means your query cache size isn’t big enough.
• If Opened_tables is increasing you probably have table_cache too small.
• If you have a lot of connections, check Threads_created in show status; If it keeps increasing, you need to up the thread_cache_size in my.cnf. The max connections in MySQL are default at 200.

One large table vs many small tables

http://forum.mysqlperformanceblog.com/s/m/1511/#msg_1511

Partitioning in mysql

http://jayant7k.blogspot.com/2006/08/mysql-partitioning.html