How to start first
- Make sure you are using utf8 instead of latin1. This will help you to avoid annoying problems in long run
- If your table size is at the level of GBs, it is better to use InnoDB instead of MyISAM as the storage engine. Transactions are supported better in InnoDB. For large tables, this will increase the performance, especially when you want to write to the tables.
- You should enable "log_slow_queries" and "long_query_time" in "my.cnf", otherwise you don't see where the bottleneck is. After you enable these two fields and run your DB for about 1 day with normal usage, you can run MySQL tuner (should be accessible via your package manager, or www.mysqltuner.pl) to examine those MySQL logs. MySQL tuner will give you some suggestions on how to tune my.cnf.